MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Mariella Petrini Date:September 10 2008 7:22pm
Subject:RE: SELECT DISTINCT with ORDER BY implementation
View as plain text  
I had run those but my question is related to why there is a the different behaviour
between SIMPLE and DERIVED query  in case b):



The SIMPLE query it does use the index info1 for the same query.

I have set MySQL server to use "log-queries-not-using-indexes" and the
SIMPLE query does not get recorded into the slow log and EXPLAIN shows
it before executing the query.



Why  in the b) case even though I forced to use the same index info1 in both cases
SIMPLE and DERIVED (in the show warnings the output related to the derived part is
identical to the simple query rewrite)
the index was not taken in the derived and a full scan was executed ?
(type="ALL" and key="info1" )
Did I misread the output of explain in the DERIVED case ?
Is that because of the filesort in derived tables ( I mean is because it  needs to
ORDER on a field that is not part of the index) ? 


Why in case a) THE DERIVED and SIMPLE are executed in the same way ?


Thanks,

Mariella














My question was 

--- On Wed, 9/10/08, Rick James <rjames@stripped> wrote:
From: Rick James <rjames@stripped>
Subject: RE: SELECT DISTINCT with  ORDER BY implementation
To: mariellapetrini@stripped, internals@stripped
Cc: mysql@stripped
Date: Wednesday, September 10, 2008, 10:18 AM

Does EXPLAIN EXTENDED (plus SHOW WARNINGS) answer some of the questions?

 
Rick James
MySQL Geeks - Consulting & Review

 

> -----Original Message-----
> From: Mariella Petrini [mailto:mariellapetrini@stripped] 
> Sent: Tuesday, September 09, 2008 9:17 PM
> To: internals@stripped
> Cc: mysql@stripped
> Subject: SELECT DISTINCT with ORDER BY implementation
> 
> Hello,
> 
> I would like to ask a couple of questions related to SELECT 
> DISTINCT and ORDER BY in MySQL 5.0.x and 5.1.x
> 
> 1)
> Given a table 
> 
> CREATE TABLE `sort_table` (
>   `id` int(10) unsigned NOT NULL,
>   `sort` datetime NOT NULL,
>   `info1` int(10) unsigned NOT NULL,
>   `info2` int(10) unsigned NOT NULL,
>   `info3` int(10) unsigned NOT NULL,
>   KEY `info1` (`info1`,`info2`,`info3`),
>   KEY `info4` (`info1`,`info2`,`info3`,`sort`)
> ) ENGINE=... DEFAULT CHARSET=utf8;
> 
> and executing 
> 
> SELECT DISTINCT (id) from sort_table WHERE info1=.. and 
> info2=... and info3=...AND (sort >= '...') and (sort <= 
> '...') ORDER BY sort DESC
> 
> Is that query always implemented as (like )
> 
> SELECT id from sort_table WHERE info1=.. and info2=... and 
> info3=...AND (sort >= '...') and (sort <= '...') 
> GROUP BY id ORDER BY sort DESC
> 
> 
> 2)
> The sort_table stats are up-to-date (ANALYZE (and OPTIMIZE) 
> command has been run)
> When executing the following query
> 
> select DISTINCT(id)  from (select id from sort_table  where 
> info1=.. AND info2=.. and info3=.. and  (sort >='.....' AND 
> sort <= '.....') ORDER BY sort DESC) t;
> 
> a)
> 
> If the index info4 (`info1`,`info2`,`info3`,`sort`) is chosen
> then the bahaviour of the SIMPLE Query is identical to the 
> DERIVED Query (select_type=DERIVED)
> 
> explain select id from sort_table FORCE INDEX (info4)  where 
> info1=... AND info2=... and info3=... and  (sort >='.....' 
> AND sort <= '......') ORDER BY sort DESC;
> +----+-------------+------------+-------+---------------+-----
> --+---------+------+------+-------------+
> | id | select_type | table      | type  | possible_keys | key 
>   | key_len | ref  | rows | Extra       |
> +----+-------------+------------+-------+---------------+-----
> --+---------+------+------+-------------+
> |  1 | SIMPLE      | sort_table | range | info4         | 
> info4 | 20      | NULL |   16 | Using where | 
> +----+-------------+------------+-------+---------------+-----
> --+---------+------+------+-------------+
> 1 row in set (0.00 sec)
> 
> mysql> explain select DISTINCT(id)  from (select id from 
> sort_table FORCE INDEX (info4)  where info1=... AND info2=.. 
> and info3=... and  (sort >='......' AND sort <=
'......') 
> ORDER BY sort DESC) t;
> +----+-------------+------------+-------+---------------+-----
> --+---------+------+------+-----------------+
> | id | select_type | table      | type  | possible_keys | key 
>   | key_len | ref  | rows | Extra           |
> +----+-------------+------------+-------+---------------+-----
> --+---------+------+------+-----------------+
> |  1 | PRIMARY     | <derived2> | ALL   | NULL          | 
> NULL  | NULL    | NULL |   16 | Using temporary | 
> |  2 | DERIVED     | sort_table | range | info4         | 
> info4 | 20      | NULL |   16 | Using where     | 
> +----+-------------+------------+-------+---------------+-----
> --+---------+------+------+-----------------+
> 
> 
> b)
> In the case the index "info1" is chosen
> select DISTINCT(id)  from (select id from sort_table FORCE 
> INDEX (info1)  where info1=.. AND info2=.. and info3=.. and  
> (sort >='.....' AND sort <= '.....') ORDER BY sort
DESC) t;
> 
> The EXPLAIN shows
> +----+-------------+------------+------+---------------+------
> -+---------+------+------+-----------------+
> | id | select_type | table      | type | possible_keys | key  
>  | key_len | ref  | rows | Extra           |
> +----+-------------+------------+------+---------------+------
> -+---------+------+------+-----------------+
> |  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL 
>  | NULL    | NULL |   16 | Using temporary | 
> |  2 | DERIVED     | sort_table | ALL  | info1         | 
> info1 | 12      |      |   16 | Using filesort  | 
> +----+-------------+------------+------+---------------+------
> -+---------+------+------+-----------------+
> 
> In this case the "key" shows up as info1, but the
type="ALL"
> 
> while the behaviour in a SIMPLE QUERY would be (type=ref)
> 
> EXPLAIN select id from sort_table FORCE INDEX (info1)  where 
> info1=.. AND info2=.. and info3=.. and  (sort >='.....' AND 
> sort <= '.....') ORDER BY sort DESC
> 
> +----+-------------+------------+------+---------------+------
> -+---------+-------------------+------+-----------------------------+
> | id | select_type | table      | type | possible_keys | key  
>  | key_len | ref               | rows | Extra                       |
> +----+-------------+------------+------+---------------+------
> -+---------+-------------------+------+-----------------------------+
> |  1 | SIMPLE      | sort_table | ref  | info1         | 
> info1 | 12      | const,const,const |   16 | Using where; 
> Using filesort | 
> +----+-------------+------------+------+---------------+------
> -+---------+-------------------+------+-----------------------------+
> 
> 
> Is there any way to force in b) case the same behaviour 
> (optimization) of the SIMPLE query in the DERIVED query 
> (select_type=DERIVED)?
> Is there a better way of implementing case b) (without using 
> case a)) ?
> 
> Thanks in advance for your help,
> 
> Mariella
> 
> 
> 
> 
> 
> 
> 
>       
> 
> -- 
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    
> http://lists.mysql.com/internals?unsub=1
> 
> 



      
Thread
SELECT DISTINCT with ORDER BY implementationMariella Petrini10 Sep
  • RE: SELECT DISTINCT with ORDER BY implementationRick James10 Sep
    • RE: SELECT DISTINCT with ORDER BY implementationMariella Petrini10 Sep
RE: SELECT DISTINCT with ORDER BY implementationMariella Petrini11 Sep
  • Re: SELECT DISTINCT with ORDER BY implementationSergey Petrunia12 Sep
RE: SELECT DISTINCT with ORDER BY implementationMariella Petrini11 Sep