CODEWITHSUNDEEP
mysqlruby-on-rails
Daniel
Daniel

Reputation: 7172

Rails 2.3.5 / MySQL key preference issue; force index

Greetings,

I'm looking a rather "large..." table with a couple of million rows, and for simplicity sake the table looks like this

create table data (
   user_id int ...
   created_on datetime ...
   ...
   key (user_id),
   key (created_on)
   ...
) engine = InnoDB;

So, on a query... SELECT * FROM data WHERE user_id = X and created_on = Y

The query optimizer has been preferring created_on over user_id, now I can use FORCE INDEX to "force" the issue, but I don't want to have to do that for every Data.find and every other query we have (hundreds of changes).

Can I influence Rails 2.3.5 to globally use "data FORCE INDEX (user_id_index)" when possible (and no other contradictions; ie.. another FORCE index manually entered)

or

Can I change how the mysql query optimizer prefers indexes on a more manual basis? I've looked at running analyze table data; but the query plan doesn't change.

-daniel

Upvotes: 1

Views: 489

Answers (1)

zerkms
zerkms

Reputation: 254906

Create composite index user_id + created_on instead of just user_id and everything will be fine.

Upvotes: 1

Related Questions