Creating an index for a table.

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.

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.

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.

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.

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.

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.

I see. Thanks Scott.