MySQL Forums
Forum List  »  MyISAM

Re: Select query too slow from merge table of 1 billion records
Posted by: Rick James
Date: February 24, 2010 06:20PM

No to most of what you said. Here's why...

In general, MySQL will use as many fields of the index as it can, starting from the left. However, the first field(s) need to be "=", not "range" (like you are doing with c_end_datetime). Once it gets to a field that is "range" (or GROUP BY or ORDER BY), it will use that field and then stop.

So, WHERE c_end_datetime >= '...' AND xxx = '...' will use _only_ the first field of INDEX(c_end_datetime, xxx). On the other hand, both parts of INDEX(xxx, c_end_datetime) would get used.

Similarly, your suggested 6-field index would not be useful.

This sounds like a typical Data Warehouse application? With 'reports' that cover convenient timespans (hours, days, etc)? And the fields fetched are COUNTs, SUMs, AVGs? I don't see them in your SELECTs, but maybe you have other SELECTs like that? If so, let's talk about "summary" tables. They could easily make the 'reports' run 10x faster.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Select query too slow from merge table of 1 billion records
3023
February 24, 2010 06:20PM


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.