MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Mariella Petrini Date:September 10 2008 10:47pm
Subject:RE: SELECT DISTINCT with ORDER BY implementation
View as plain text  
I did try to rephrase my comments.
I hope they make sense.
Thanks !!!!

Mariella


--- On Wed, 9/10/08, Mariella Petrini <mariellapetrini@stripped> wrote:

> From: Mariella Petrini <mariellapetrini@stripped>
> Subject: RE: SELECT DISTINCT with  ORDER BY implementation
> To: internals@stripped, "Rick James" <rjames@stripped>
> Cc: mysql@stripped
> Date: Wednesday, September 10, 2008, 3:33 PM
> I had run those but my question regarding the DERIVED
> queries is more related to why there is a the different
> behaviour between SIMPLE and DERIVED query  in case b):
> 
> 
> The SIMPLE query in case b) 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 (use of index info1) before executing the query.
> 
> 
> In the case of the DERIVED query I forced to use the same
> index info1 as in SIMPLE query  (in the show warnings the
> rewrite output related to the derived part is identical to
> the simple query rewrite)
> but the type (access strategy) that was executed in DERIVED
> is full scan 
> (type="ALL" and key="info1" ) and the
> key is info1 ?
> 
> Did I misread the output of explain in the DERIVED case ?
> Is the different behavior between SIMPLE and DERIVED in b)
> because of the filesort in derived tables (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 (access strategy is range on the index info4,
> type=range)?
> 
> 
> Thanks,
> 
> Mariella
> 
> 
> 
> --- On Wed, 9/10/08, Mariella Petrini
> <mariellapetrini@stripped> wrote:
> 
> > From: Mariella Petrini
> <mariellapetrini@stripped>
> > Subject: RE: SELECT DISTINCT with  ORDER BY
> implementation
> > To: internals@stripped, "Rick James"
> <rjames@stripped>
> > Cc: mysql@stripped
> > Date: Wednesday, September 10, 2008, 12:22 PM
> > 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