Markus Hoenicka wrote:
Hi,
is the following behaviour intended? Are my queries wrong? The output
shows only the "Extra" field as the other fields are identical in all
cases.
EXPLAIN SELECT t_refdb.refdb_id FROM t_refdb WHERE t_refdb.refdb_id>0
ORDER BY t_refdb.refdb_id;
=> Using where; Using index (results are sorted)
EXPLAIN SELECT DISTINCT t_refdb.refdb_id FROM t_refdb WHERE
t_refdb.refdb_id>0 ORDER BY t_refdb.refdb_id;
=> Using where; Using index (results are sorted)
EXPLAIN SELECT t_refdb.refdb_id FROM t_refdb WHERE refdb_type!='DUMMY'
AND t_refdb.refdb_id>0 ORDER BY t_refdb.refdb_id;
=> Using where; Using filesort (results are sorted)
EXPLAIN SELECT DISTINCT t_refdb.refdb_id FROM t_refdb WHERE
refdb_type!='DUMMY' AND t_refdb.refdb_id>0 ORDER BY t_refdb.refdb_id;
=> Using where (results are not sorted)
That is, if MySQL can't use an index to sort the result, DISTINCT
queries won't be sorted at all.
That doesn't make sense.
How many rows fit that criteria?
ie: select count(distinct t_refdb.refdb_id) FROM t_refdb WHERE
refdb_type!='DUMMY' AND t_refdb.refdb_id>0;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
