> GOD! OK, sorry, I wasn't quite expecting this:
> Wow!
:)
>
> But what's the explanation for this huge improvement? Again, I
> was always told the opposite, and the Manual itself says: ...
Yes, and it is true (usually). But your EXPLAIN showed a filesort and
that is bad. What happens is that if the resultset is sorted only on the
first few characters (based on the index) of that column. Since you
requested an ORDER BY, it had to go back and fully sort the resultset.
If you have the index do the whole column, then this step is not needed.
Even better is that due to the limit, it can safely go right to the part
of the table it needs to, and once it gets the 5 rows, it is done
(rather then getting all of them for the sort step). Even better in this
particular case is that all the information needed is in the index (the
MYI file) so it did not even need to do a read on the data file (MYD).
Less disk access is a good thing...
> Hmm. When I returned to the multiple-table query that started
> this thread,
And it was slow. Yeah, one thing at a time. It makes it easier for
people reading this list now or in the future (if it comes up in a
search result) if we go over things one item at a time.
Since I never saw the whole table definitions (the indexes in
particular), I'll have to try and guess through it. So try this:
ALTER TABLE cg add index(q_id,cw);
Tell me how that works and send the EXPLAIN.
The point here is that now you are doing a join and you are using both
columns to qualify the resultset. So we should use a composite index
rather than have individual ones (of which MySQL will choose only one).
Also, you can change line
AND cg.cw BETWEEN 't' AND 'tzzz'
To
AND cg.cw like 't%'
For better readability (how many zzz's are enough, eh?). Personal
preference.
--steve-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
