[bono56]

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

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?

[r937]

here is my rule of optimization: if you need the results in a particular order, use ORDER BY -- otherwise, don't

simple, innit

[bono56]

i need it, but for me it was odd, this fld has index with Cardinality=1.

[RBARAER]

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 :

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

[r937]

the key word being "seems"

[RBARAER]

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

[r937]

yes i would

[georgev]

This public service announcement was brought to you by....

[bono56]

another point that was strange for me:
look at these queries executed on a table with 700,000 record:

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?

[aschk]

Perhaps because you have an index on the flag2 field?

[Zamolxe]

aschk is right, putting an index on that field might speed things up. i have a similar problem:

 ( 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