This week's book giveaway is in the Cloud/Virtualizaton forum.
We're giving away four copies of Mesos in Action and have Roger Ignazio on-line!
See this thread for details.
Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Creating an index for a table.

 
Susan Smith
Ranch Hand
Posts: 224
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm trying to create an index for a table. I'm trying to figure out the best way to do this. I came out with two options. Are they basically the same? Or is there something that I don't notice.

1.)


2.)


The other option that I'm thinking to do is to create primary key but it doesn't seem necessary since primary key is only necessary when I need to build referential integrity, right?

Thanks in advance for the help.
 
Susan Smith
Ranch Hand
Posts: 224
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And the SQL commands that I mention in my first post above is very very slow with huge tables. Is there anyway to improve this?

Thank you.
 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't know which of those two statements is the one you want. But yes, it does take a long time to build an index over a huge table. There's no getting around that. The good thing is, you're only going to build the index once. If you were going to do it on a daily basis, that might be a problem.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4014
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The difference is what the database does in the background. Both enforce a uniqueness constraint, but the "INDEX" guarantees a database-backed index is available. Often "UNIQUE KEY" does this too, so its not a huge difference. For indexes, you shoul[d specify whether you want a hash or tree depending on what you need. You can have indexes seperate from primary keys. Primary Keys (and other keys like foreign/unique) are more about referential integrity whereas Indexes are about performance.
 
Susan Smith
Ranch Hand
Posts: 224
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Paul + Scott,

Scott,
Often "UNIQUE KEY" does this too, so its not a huge difference

You mentioned 'often'. Is there any case "UNIQUE KEY" won't do this?

Thanks again.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4014
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You need to read your database documentation. Its hard to say what its doing behind the scenes. I would think in order to best enforce a uniqueness constraint you would want a hash index, but its really up the DBMS.
 
Susan Smith
Ranch Hand
Posts: 224
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I see. Thanks Scott.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic