Eli wrote:
> The reason I thought about this is that I got several tables that hold
> their own specific data and are indexed inside themselves. But I got an
> issue to search on JOIN of 2 or more tables with comparison and/or
> ordering on combinations of fields from the various tables. This usualy
ORDER BY only uses an index in very specific circumstances IMHO. When
using JOINs you will probably end up with MySQL doing a filesort.
http://dev.mysql.com/doc/mysql/en/order-by-optimization.html
states that it will NOT use indexes if:
- the columns in the ORDR BY are not all from the first non-constant
table in the execution plan
- the ORDER BY and GROUP BY expressions are different
- the index type does not store the rows in order (e.g. HASH index)
This makes the chances pretty slim for complex queries that it will use
an index for sorting. One of the upgrades we recently did resulted in
more execution plans which used an index for sorting. We have the
impression that older versions of MySQL like to have less records to
consider, but that newer versions slightly prefer an execution plan with
more records that can actually use an index for sorting.
> ended up in a case that the first table in the JOIN used his own index,
> while the rest had to do full table scan, so thought that if there was a
> spanned index it would be much faster.. Unfortunately, as I thought, it
> is impossible (for now at least)..
Optimizing order by random would also be a welcome improvement. I have
the impression that it is currently implemented by adding a column with
random values and after collecting all the data executing a filesort on
that column. After that the LIMIT, etc. will be used to select the data
it will send to the client.
IMHO it would be faster to take the limit into account at an earlier
stage and using the random part to determine whether the record it is
now considering would belong within the limit-selection or not.
There will be caveats for sure, but it may be worth considering...
Regards, Jigal.
