multiple key indexing 1

[mbabcock]

I created a simple lookup table with these columns:

iid = auto-inc, primary key
cReference = varchar(30), reference to owner/entity
cCode = varchar(16), abbreviation used as FK in other tables
cDescription = varchar(64), description for cCode

I've got a primary index on iid (obviously), and I was going to add an index on cReference too since so many stored procs (responsible for retrieving lookup data) would use the cReference in the WHERE clause. My question is this: I should probably add a candidate index on cReference + cCode to make sure no duplicates get in, but is that the best indexing strategy as you see it? I wondered if the single IDX on just the cReference field was necessary since I was going to have another index on cReference + cCode. I believe that the latter would suffice in MySQL, but I'm not sure about SQL Server.

tia!
--Michael

 

[MikeBronner]

One thing to keep in mind about primary keys and foreign keys is that it is always best to keep them numerical (i.e. as INT or BIGINT). The reason for this is that indexing is many times faster when processing numerical columns, than textual columns, as those will require full-text indexing to efficiently join tables (which is still slower).

As for you question:
Any columns that appear often in WHERE clauses should be indexed, as this will greatly speed up your queries (and thus your application). However, this doesn't mean that ALL columns in your WHERE clauses need to be indexed. You will need to evaluate this based on number of records, number of distinct column values, and so forth (the greater the number, the higher the need for indexing).

In your case it makes sense to create a unique constraint based on the columns cReference + cCode (not an index). This will not eliviate the need for you primary key, as that will be used in your where clauses. Getting rid of the primary key would require additional entries in your WHERE clause, and would significantly increase processing and thus slow your query down (instead of just having to check the primary key field, it now has to check two textual columns).

I hope I didn't confuse the situation more. Let me know if I can be of further assistance.

Take Care,
Mike

 

[mbabcock]

Hi Mike,

Good advice, and I had added the unique constraint for data integrity. But my real question is "how many indexes (beyond the PK): one for cReference and one for cReference+cCode or would just one for cReference+cCode suffice? (iow, does having both indexes--cReference AND cReference+cCode--make the lone cReference index just overkill/overhead?)

--Michael

 

[MikeBronner]

Let me counter-question: what is the intended benefit of having an index on cReference+cCode?
I'm assuming that the primary key is actually a foreign key in another table, in which case you should need any other indexes on the table.

If you are searching directly against either of the fields cReference or cCode you should put indexes on each seperately.

If you only ever search against the combination of cReference+cCode you should make an index for cReference+cCode.

If in fact you aren't searching against these fields, then an index is unnecessary.

I would advise against getting rid of the primary key, as that will make joins faster.

Take Care,
Mike

 

[mbabcock]

We're on the same page...thanks. I never meant to give the impression that the PK on iid was going away...that's a given to be there for me. ;-)

I think I was mixing up constraints and candidate indexes. You've made it clear now. Thanks!

--Michael