Multi-column database indexes and query speed

Question

I'm deploying a Rails application that aggregates coupon data from various third-party providers into a searchable database. Searches are conducted across four fields for each coupon: headline, coupon code, description, and expiration date.

Because some of these third-party providers do a rather bad job of keeping their data sorted, and because I don't want duplicate coupons to creep into my database, I've implemented a unique compound index across those four columns. That prevents the same coupon from being inserted into my database more than once.

Given that I'm searching against these columns (via simple WHERE column LIKE %whatever% matching for the time being), I want these columns to each individually benefit from the speed gains to be had by indexing them.

So here's my question: will the compound index across all columns provide the same searching speed gains as if I had applied an individual index to each column? Or will it only guarantee uniqueness among the rows?

Complicating the matter somewhat is that I'm developing in Rails, so my question pertains both to SQLite3 and MySQL (and whatever we might port over to in the future), rather than one specific RDBMS.

My guess is that the indexes will speed up searching across individual columns, but I really don't have enough "under the hood" database expertise to feel confident in that judgement.

Thanks for lending your expertise.

Answer 1

will the compound index across all columns provide the same searching speed gains as if I had applied an individual index to each column?

Nope. The order of the columns in the index is very important. Lets suppose you have an index like this: create unique index index_name on table_name (headline, coupon_code, description,expiration_date) In this case these queries will use the index

select * from table_name where headline = 1
select * from table_name where headline = 1 and cupon_code = 2

and these queries wont use the unique index:

select * from table_name where coupon_code = 1
select * from table_name where description = 1 and cupon_code = 2

So the rule is something like this. When you have multiple fields indexed together, then you have to specify the first k field to be able to use the index.

So if you want to be able to search for any one of these fields then you should create on index on each of them separately (besides the combined unique index)

Also, be careful with the LIKE operator.

this will use index SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'; and this will not SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';

index usage http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html multiple column index http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html