MySQL Forums
Forum List  »  Performance

combined index ignored
Posted by: Daniel Blackhurst
Date: January 21, 2010 04:02PM

Hi I have a fairly basic query that I cant seem to get running quick

SELECT * FROM `gps_positions` where id >= 949217322 AND id <= 950585422 AND vehicle_id = 21481

tried an index on (vehicles_id,id) and (id,vehicles_id) with no joy. We have about 4000 vehicles putting on average about 1000000 gps_positions between them everyday so its not exactly a small database. Would it be possible without doing a huge scan?

Sometimes it can take a couple of minutes although most of the time its 200ms. I'm guessing this is due to buffers and query caches although I need it to always be under the second mark.

If this index cant be done, what would be the next step? More RAM? We have a 24GB database and growing, with around about 10GB of that been hot the rest needed for reports. Currently only got 8GB with 4GB innodb buffer pool. Not even sure how to find out the total size in MB of an index to see if it could fit into memory.

Any help would be great,

Cheers, Dan

Edited 1 time(s). Last edit at 01/22/2010 05:31AM by Daniel Blackhurst.

Options: ReplyQuote

Written By
combined index ignored
January 21, 2010 04:02PM
January 22, 2010 09:47PM
January 23, 2010 04:49AM
January 23, 2010 01:23PM
January 23, 2010 03:40PM
January 24, 2010 01:05AM
January 24, 2010 07:29AM
January 24, 2010 03:40PM
February 02, 2010 09:09AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.