Re: combined index ignored

[Daniel Blackhurst]

Sorry I sent you some of the things you asked for off the live and some off my local. I have added the index (vehicle_id,id) although it won't use it for some reason. Should have used the correct create statement sorry. If you see the EXPLAIN that shows the query on my local copy that is of allot smaller size. Takes too long to add indexes on the live without testing fully.

I didn't know that indexes contained the primary key also in innodb. Do you know any references where I could look at that in more detail?

The machine is 64bit. Do you think the small buffer pool size in comparison to the db size could be the culprit for this query running really slow occasionally?

Thanks for the advise about the columns. Data is archived and deleted from the database over 3 months as its very rarely needed. I think key rotation might be an idea for the large primary key.

Any ideas on how to force MYSQL to use that key? Even if I remove the vehicle_id,date_time index it then chooses the PRIMARY KEY.

Cheers,

Dan