Thank you for reply, Rick!
>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.
Now I decided to use FORCE INDEX, when count of nodes with same tid more than 1000, because it work faster.
Also, vid is AUTO_INCREMENT, created = TIMESTAMP on table node, may be I should sort table term_node by tid instead of vid?
>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'.
I have read
http://dev.mysql.com/doc/refman/5.1/en/limit-optimization.html and tried to achieve "If ordering is done by using an index, this is very fast."
>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.
Actually I have Dual Xeon E5620 and Hardware raid with 256Mb cache.
I'm running web site based on drupal and this tables belongs to it.
This query constructed with it's module Views.
And this is what I get in analyzed slow_query.log for original query:
### 14199 Queries
### Total time: 16760.306372, Average time: 1.18038639143602
### Taking 0.500113 to 6.778163 seconds to complete
### Rows analyzed 13674 - 126552
So, there is a moments when this query take 6 seconds instead of 0.3 (several tid's applied)
And this variables grows:
Sort_merge_passes = 556
Created_tmp_disk_tables = 3,260 k
This query is about 75% of all slow queries. What can I do to eliminate it from slow_query?
Edited 1 time(s). Last edit at 12/18/2010 01:16AM by Andrey Petukhov.