Quote
> "why query that use FORCE INDEX work slower than
> original with filesort?" -- did you run the query
> twice, to avoid caching differences?
I have set SQL_NO_CACHE in SELECT statement.
FORCE INDEX and filesort have to do with the execution of the query. SQL_NO_CACHE is totally unrelated -- it has to do with the "Query cache", and says not to use it. (This is good for benchmarking.)
Roughly, this is the sequence of events on a SELECT:
1. If the SELECT is in the Query cache (and you did not say SQL_NO_CACHE), then deliver the resultset from the QC (and quit). Else...
2. Decide how to evaluate the SELECT, taking into account FORCE INDEX, etc.
3. Evaluate
4. Deliver the resultset (and store in the QC if appropriate).
"ORDER BY ... LIMIT is made with indexes" -- It depends. If it can use an index, then it will be fast. As a general rule, if the WHERE clause hits more than one table (as does yours), no INDEX will be of much use. You have hit a half-exception -- and it is fast sometimes.
"I have set .5, but first i would like to eliminate I/O problems with scan of tables." -- There are 3 issues here.
* long_query_time can be set independently.
* I/O -- if you have good caching, you can avoid I/O, but still be somewhat slow -- It will be CPU bound scanning lots of rows
* "Table scan" -- sometimes this is the best way to do a query.
Let me rephrase where I am going with this. The _goal_ is to speed up the query. The _symptoms_ are "filesort", large "Rows examined", "table scan", etc. Eliminating any of the symptoms may, or may not, be the right approach to speeding up the query.
When these 3 items are present (JOIN with the WHERE hitting both tables, ORDER BY, and LIMIT), there is usually no way make the query 'fast'.
"that allocated in memory" -- There are many rules and caveats about when it will create a temp table in MEMORY, and when it has to switch to MyISAM.
"so i decided hat order is important" -- Yes. INDEX(a,b) and INDEX(b,a) are quite different:
WHERE a=1 AND b=2 -- They work equally well.
WHERE a=1 AND b>2 -- INDEX(a,b) works better.
WHERE a>1 AND b>2 -- Neither one gets past the first field.
WHERE b=1 -- INDEX(b,...) only
WHERE b>1 -- INDEX(b,...) only
"Is this scan performed with rows fetched from table (disk) or with indexes?" -- The answer varies with
* key_buffer_size vs index size. (assuming MyISAM)
* spare OS memory vs data size.
* Whether the caches are cold.
* "Using temporary; Using filesort" does not necessarily mean "disk".
* Examining 172837 rows (of data or index) could well take 0.3 CPU seconds.
(I'm NOT talking about the "Query cache".)
Your key_buffer_size is plenty big, so (once it warms up), there will be no I/O for index block fetches.
You have lots of RAM (relative to the data size), so ditto for the data.
Check on monitoring (sar?) the disk activity. This may be the 'proof' of whether it is doing I/O or not.
A guestimate... If each of 172837 rows involved a disk hit (say, 10ms each), and if you have a pretty typical non-RAID drive system, then I would estimate 30 _minutes_ to perform the query. That virtually 'proves' (to me), that the 0.3 seconds is all CPU.