the mysql query optimizer is somehow stupid
a simple query, order by with a indexed column and
you have to use where order_by_field0 - why the
hell is mysqld not happy that a key is on the field
used in order by?
mysql EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC;
Hi,
On Wed, Jul 11, 2012 at 10:31 AM, Reindl Harald h.rei...@thelounge.net wrote:
the mysql query optimizer is somehow stupid
Its not stupid - remember its not trying to find the best index,
rather its trying to find the least costly plan
to return the data in the quickest manner.
For the
Am 11.07.2012 11:43, schrieb Ewen Fortune:
Hi,
On Wed, Jul 11, 2012 at 10:31 AM, Reindl Harald h.rei...@thelounge.net
wrote:
the mysql query optimizer is somehow stupid
Its not stupid - remember its not trying to find the best index,
rather its trying to find the least costly plan
to
column used in the order by caluse, should be the first column in the
select statement to make the index work
On Wed, Jul 11, 2012 at 3:16 PM, Reindl Harald h.rei...@thelounge.netwrote:
Am 11.07.2012 11:43, schrieb Ewen Fortune:
Hi,
On Wed, Jul 11, 2012 at 10:31 AM, Reindl Harald
On Wed, Jul 11, 2012 at 5:46 AM, Reindl Harald h.rei...@thelounge.netwrote:
mysql show profiles;
+--++--+
| Query_ID | Duration | Query
my reason for create a key on qg_sort was primary
for this query - but why is here 'filesort' used?
mysql EXPLAIN SELECT * FROM cms1_quickbar_groups ORDER BY qg_sort ASC;
++-+--+--+---+--+-+--+--++
| id |
Hi,
The statement will do a Full table scan, because of the following things :
Not using Where clause, and selecting all columns (*) within the query.
Filesort is used since no index is used, use a where clause with condition
on column which is indexed and notice the explain plan. Also you can