Geek asks and answers

Multi-Column Database Indexes and Query Speed

I am deploying a Rails application that combines coupon data from various third-party providers into a searchable database. Searches are carried out in four fields for each coupon: title, coupon code, description and expiration date.

Since some of these third-party providers do a pretty poor job of sorting their data, and also because I don't want duplicate coupons populating my database, I implemented a unique composite index in these four columns. This prevents inserting the same coupon into my database more than once.

Given what I'm looking for against these columns (through a simple comparison WHERE column LIKE %whatever%so far), I want these columns to be separately winnings from the speed that would need to be indexed.

So, my question is: will the combined index across all columns provide the same increase in search speed, as if I applied a separate index to each column? Or does it only guarantee uniqueness between the lines?

The complication of the question is that I am developing in Rails, so my question is about both SQLite3 and MySQL (and whatever we carry over in the future), and not just one specific RDBMS.

I suppose indexes will speed up searches on individual columns, but I really lack the knowledge of the database “under the hood” to feel confident in this proposition.

Thank you for giving us your knowledge.

+3
1

, ?

. . , : create unique index index_name on table_name (headline, coupon_code, description,expiration_date)

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

:

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

, . , , k, .

, , , ( )

, LIKE.

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'; SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';

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

+5

More articles:


All Articles