MySql select from subquery slow (even though subquery on it's own is fast)

I'm trying to speed up this query:

SELECT order.id
    FROM (
   开发者_Go百科  SELECT o.id 
     FROM `order` as `o` 
     INNER JOIN order_item as oi on
        oi.order_id = o.id
        AND `o`.`canceled` = 0
        AND ( `o`.`return_date` > "2011-03-14" OR oi.checked = 0 OR '2011-03-14' < oi.last_update)
    ) as `order`

It clocks at 0.0930 seconds. The subquery (SELECT * FROM order as o...) on it's own clocks at 0.0005 seconds. The table I'm testing on has about 10000 rows, 43 rows are returned from subqueries where-clause.

Except from speeding up the query, I'd really like it if someone could explain to me why the query becomes more than 100 times slower when I wrap it within another query?

MySql explain tells me that the subquery on it's own first selects o, and then oi. MySql explain for the whole query first selects (what is derived2?), then o and oi in that order.

I'd like the subquery to be a subquery because I'm doing a lot of joins which aren't in the where clause (which I've excluded from the code and the benchmarks). If I'd use the subquery on it's own, with the joins, the query would be even slower.

Any help would be much appreciated. I've searched for answers but can't find them, and that might just be because I do not know what to look for, if so, I apologize.

MySQL buffers the inline view before doing any operations on it.

0.0006 seconds you see for the first query is most probably the response time (the first row fetched), not the overall time (the last row fetched).

If id is a PRIMARY KEY on your order table, you don't need a GROUP BY on it.

If you have lots of items per order (more than about 20 items per order in average or so), you can try creating the following indexes:

order_item (order_id, checked)
order_item (order_id, last_update)

and splitting your correlated subquery in two:

SELECT  *
FROM    order o
WHERE   o.canceled = 0
        AND
        (
        o.return_date > '2011-03-14'
        OR
        (
        SELECT  MIN(checked)
        FROM    order_item oi
        WHERE   order_id = o.id
        ) = 0
        OR
        (
        SELECT  MAX(last_update)
        FROM    order_item oi
        WHERE   oi.order_id = o.id
        ) > '2011-03-14'
        )

Again, this will only help if you have lots of orders per items, otherwise a single scan on all orders will be more efficient.

1. check indexes
2. did you optimize tables that you are working with ?
3. query will work faster if you'll change "*" in the SELECT statement to actual names of columns that you need.
4. do you need multiple GROUP BY "order id" - one inside another?