Re: Slow query with order by and limit
Rick James Wrote:
-------------------------------------------------------
> "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.
> I frown on FORCE INDEX because it ties the hands
> of the optimizer. If the data is unevenly
> distributed, some other index may be better when
> you provide a different constant in the WHERE
> clause. Your data seems to be unevenly
> distributed.
Optimizer always suggest to use second table, then filesort - but it heart when affected rows more than 1k - in that case FORCE INDEX better.
> "Rows_examined: 172837" -- It has to work that
> hard because of the complexity of the query. It
> cannot do the LIMIT until the ORDER BY (sort) is
> done. It cannot use any index to help with the
> ORDER BY because of needing to gather data from
> two tables (JOIN). So, it gathers all the
> possible rows from one table, then reaches into
> the other table (random probe each time), puts
> that info with the first. Then it sorts the data
> and delivers 3 rows.
I thought, when both tables in explain get using index, then ORDER BY ... LIMIT is made with indexes, that allocated in memory, so query is quite fast, but it doesn't.
> I see little use for this:
> log_queries_not_using_indexes = 1
> I prefer to decrease the value of long_query_time.
> 2 (seconds) is a good default. In your case, you
> would have needed 0.3 or less to catch this
> query.
I have set .5, but first i would like to eliminate I/O problems with scan of tables.
> I don't see the definition of created_vid. Is it
> INDEX(created, vid)? Using that index avoids the
> sort phase, but may have to scan through a lot of
> irrelevant rows to find the desired 3 rows. This
> is confirmed by "Rows_examined: 172837".
Yes i created index (created,vid). When i tried to use vid_created i've got filesort, so i decided hat order is important.
Is this scan performed with rows fetched from table (disk) or with indexes?
I appreciate you for replies.
Edited 2 time(s). Last edit at 12/17/2010 10:14AM by Andrey Petukhov.
Subject
Views
Written By
Posted
4972
December 16, 2010 07:43AM
1466
December 17, 2010 12:55AM
2025
December 17, 2010 05:43AM
1293
December 17, 2010 05:59AM
1156
December 17, 2010 07:58AM
1150
December 17, 2010 08:20AM
1254
December 17, 2010 09:04AM
2335
December 17, 2010 09:29AM
1166
December 17, 2010 09:53AM
Re: Slow query with order by and limit
1182
December 17, 2010 10:04AM
1610
December 17, 2010 11:47PM
1288
December 18, 2010 12:39AM
1263
December 18, 2010 10:50AM
998
December 20, 2010 04:46AM
1079
December 17, 2010 09:31AM