Results 1 to 11 of 11
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: execution time of a query with & without ORDER BY

    hi
    recently i wonder why "ORDER BY" increase execution time of a query?
    i run these two query over a indexed field (flag1):

    Code:
    SELECT * FROM tbl1 WHERE 1 =1 AND flag1=1 LIMIT 10(Query took 0.2401 sec)
    SELECT * FROM tbl1 WHERE 1 =1 AND flag1=1 ORDER BY flag1 LIMIT 10(Query took 2.1315 sec)
    u see? flag1 exist in both WHERE & ORDER BY section, but second query took 10 times more than first query. any idea about optimizin second query or table structure for decrease execution time?
    Last edited by bono56; 10-25-07 at 15:09.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here is my rule of optimization: if you need the results in a particular order, use ORDER BY -- otherwise, don't

    simple, innit
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Posts
    144
    i need it, but for me it was odd, this fld has index with Cardinality=1.

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by bono56
    i need it, but for me it was odd, this fld has index with Cardinality=1.
    Do you mean that "flag1=1" returns only 1 row ?

    You agree that in your example you don't need the "ORDER BY flag1" clause since you only select the rows with "flag1=1". It was just a test to demonstrate how the MySQL optimizer is dumb sometimes, wasn't it ?

    Just for fun, Oracle seems more intelligent :
    Code:
    rbaraer@Ora10g> select * from transactions where codevaleur = 'AAABA';
    
    11951 rows selected.
    
    Elapsed: 00:00:00.95
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=11039 Bytes=408443)
       1    0   INDEX (RANGE SCAN) OF 'CP_TRANSACTIONS' (INDEX (UNIQUE)) (Cost=85 Card=11039 Bytes=408443)
    
    
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            888  consistent gets
              0  physical reads
              0  redo size
         641474  bytes sent via SQL*Net to client
           9420  bytes received via SQL*Net from client
            798  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
          11951  rows processed
    
    rbaraer@Ora10g> select * from transactions where codevaleur = 'AAABA' order by codevaleur;
    
    11951 rows selected.
    
    Elapsed: 00:00:00.95
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=11039 Bytes=408443)
       1    0   INDEX (RANGE SCAN) OF 'CP_TRANSACTIONS' (INDEX (UNIQUE)) (Cost=85 Card=11039 Bytes=408443)
    
    
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
            888  consistent gets
              0  physical reads
              0  redo size
         641474  bytes sent via SQL*Net to client
           9420  bytes received via SQL*Net from client
            798  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
          11951  rows processed
    
    rbaraer@Ora10g>
    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the key word being "seems"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by r937
    the key word being "seems"



    In this particular case, since the execution times are the same with Oracle and the "order by" takes 9x more time with MySQL, I would even risk a "is more intelligent", wouldn't you ?

    But I know you are going to say :
    the key word being "In this particular case"
    so I won't go any further

    I have a lot of experience with Oracle, but quite few with MySQL and I'm interested in knowing more about it. From your experience, would you say that the MySQL optimizer generally works well ?

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes i would
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This public service announcement was brought to you by....
    George
    Home | Blog

  9. #9
    Join Date
    May 2004
    Posts
    144
    another point that was strange for me:
    look at these queries executed on a table with 700,000 record:
    Code:
    SELECT * FROM tbl1 WHERE flag2='X' ORDER BY fld1 (return 50,000 total)
    SELECT * FROM tbl1 WHERE flag2='Y' ORDER BY fld1 (return 5,000 total)
    despite of number of records return with the where criteria, execution time is the same, why?
    i supposed mysql first select records have that criteria, then go to sort them, so execution time must depends on number of records?

  10. #10
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Perhaps because you have an index on the flag2 field?

  11. #11
    Join Date
    Feb 2004
    Location
    Bucharest
    Posts
    37

    Post re

    aschk is right, putting an index on that field might speed things up. i have a similar problem:
    Code:
     ( SELECT SQL_CALC_FOUND_ROWS  [..fields here..] FROM `table_tmp` INNER JOIN `cat`  ON `cat`.`cat_id` = `table_tmp`.`cat_id` WHERE  `flag` = '1' AND `price1` > 0 AND `price2` > 0 AND `price3` > 0 AND `price4` > 0 AND `table_tmp`.`cat_id` IN (1,2,3,4) ) LIMIT 0,25
    this query runs in 1 second. when i add ORDER BY `flag` DESC, `price1` ASC it runs in 3.2 seconds. i have indexes on `flag` and on `price`. i've added an index on `flag` + `price1` and the query runs now in 2.05 sec. how can i improve it better? thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •