MySQL Query Optimization - Avoiding Subqueries

Question

I need to update some rows in my table, for simplicity called "three".

I select the columns to update with this query:

SELECT one.id
FROM one 
    JOIN `two` ON ( one.id = two.page )
    JOIN `three` ON ( one.id = three.item )
WHERE two.level = 1
    AND  two.item = (SELECT item FROM two WHERE page = 5 AND level = 1 ) 
    AND three.position > (SELECT position FROM three WHERE item = 5 ) 
ORDER BY three.position

Now I call an update query with id's I get.

Is there any chance to eliminate the subqueries?

Edit (after Melanie's comment):

Table "one":

|id|text|

Table "two":

|id|item|page|level|

Table "three":

|item|position|

So when I run the query

SELECT item FROM two WHERE page = 5 AND level = 1

It will return f.ex 1 and the final WHERE clause will be:

two.item = 1 AND two.level = 1

Which is not the same as:

two.level = 1 and two.page = 5

I have the table one - some text with some one.id. I need to update all items from table three which has higher position than my item (f.ex. id = 5) have. But those items should also have the same two.item in table two, where two.page = one.id and level = 1

I am sorry for a poor description.

Answer 1

@TheVedge is interesting solution but does not produce the same result as your query

I suggest to avoid duplicate the same table also with a view so a little correction

Another correction is three2.item=5

I suggest to use in subquery limit 0,1 so never return more then one element

SELECT one.id
FROM one 
   JOIN `two` AS TWO2 ON (two2.page = 5 AND two2.level = 1)
   JOIN `two` ON ( one.id = two.page AND two.item = two2.item )
   JOIN `three` AS THREE2 ON ( three2.item = 5)
   JOIN `three` ON ( one.id = three.item AND three.position > three2.position)
WHERE two.level = 1
ORDER BY three.position

Remember that you are not doing the same thing with this query.

Try this

 CREATE TABLE `one` (
    `id` INT(10) NULL DEFAULT NULL,
`text` VARCHAR(50) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

_

CREATE TABLE `three` (
`item` INT(10) NULL DEFAULT NULL,
`position` INT(10) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

_

CREATE TABLE `two` (
`id` INT(10) NULL DEFAULT NULL,
`item` INT(10) NULL DEFAULT NULL,
`page` INT(10) NULL DEFAULT NULL,
`level` INT(10) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

_

    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (1, 1, 5, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (3, 3, 5, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (4, 4, 5, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (5, 5, 5, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (6, 6, 5, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (7, 7, 5, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (8, 8, 5, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (9, 9, 5, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (2, 2, 5, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (10, 2, 1, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (11, 1, 1, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (13, 3, 1, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (14, 4, 1, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (15, 5, 1, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (16, 6, 1, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (17, 7, 1, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (18, 8, 1, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (19, 9, 1, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (20, 2, 2, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (21, 1, 2, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (23, 3, 2, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (24, 4, 2, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (25, 5, 2, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (26, 6, 2, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (27, 7, 2, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (28, 8, 2, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (29, 9, 2, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (30, 2, 3, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (31, 1, 3, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (33, 3, 3, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (34, 4, 3, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (35, 5, 3, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (36, 6, 3, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (37, 7, 3, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (38, 8, 3, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (39, 9, 3, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (40, 2, 4, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (41, 1, 4, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (42, 3, 4, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (43, 4, 4, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (44, 5, 4, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (45, 6, 4, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (46, 7, 4, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (47, 8, 4, 1);
    INSERT INTO `two` (`id`, `item`, `page`, `level`) VALUES (48, 9, 4, 1);

-

    INSERT INTO `three` (`item`, `position`) VALUES (1, 1);
    INSERT INTO `three` (`item`, `position`) VALUES (2, 1);
    INSERT INTO `three` (`item`, `position`) VALUES (3, 1);
    INSERT INTO `three` (`item`, `position`) VALUES (4, 1);
    INSERT INTO `three` (`item`, `position`) VALUES (5, 0);
    INSERT INTO `three` (`item`, `position`) VALUES (6, 1);
    INSERT INTO `three` (`item`, `position`) VALUES (7, 1);
    INSERT INTO `three` (`item`, `position`) VALUES (8, 1);
    INSERT INTO `three` (`item`, `position`) VALUES (9, 1);
    INSERT INTO `three` (`item`, `position`) VALUES (10, 1);
    INSERT INTO `three` (`item`, `position`) VALUES (11, 1);
    INSERT INTO `three` (`item`, `position`) VALUES (12, 1);

_

    INSERT INTO `one` (`id`, `text`) VALUES (1, 'A');
    INSERT INTO `one` (`id`, `text`) VALUES (2, 'B');
    INSERT INTO `one` (`id`, `text`) VALUES (3, 'C');
    INSERT INTO `one` (`id`, `text`) VALUES (4, 'D');
    INSERT INTO `one` (`id`, `text`) VALUES (5, 'E');
    INSERT INTO `one` (`id`, `text`) VALUES (6, 'F');
    INSERT INTO `one` (`id`, `text`) VALUES (7, 'G');

_

    SELECT 
  one.id, one.text
 ,two.id,two.item,two.page,two.level
 ,three.item,three.position
FROM one 
    JOIN `two` ON ( one.id = two.page )
   JOIN `three` ON ( one.id = three.item )
WHERE two.level = 1
   AND  two.item = (SELECT item FROM two WHERE page = 5 AND level = 1 limit 0,1 ) 
  AND three.position > (SELECT position FROM three WHERE item = 5 limit 0,1  ) 
 ORDER BY three.position




     SELECT    
 one.id, one.text
 ,two.id,two.item,two.page,two.level
 ,three.item,three.position
FROM one 
    JOIN `two` AS TWO2 ON (two2.page = 5 AND two2.level = 1)
    JOIN `two` ON ( one.id = two.page AND two.item = two2.item )
    JOIN `three` AS THREE2 ON ( three2.item = 5)
    JOIN `three` ON ( one.id = three.item AND three.position > three2.position)
 WHERE two.level = 1
 ORDER BY three.position

With original query you made a select of specific element in TheVedge solution you are joining more data So result depend on what you select

Another useful analysis is http://dev.mysql.com/doc/refman/5.0/en/show-profile.html and Explain

Show Profile show that at the first run your original query does

    Status      Duration
starting    0.000039
checking query cache for query  0.000144
Opening tables  0.000032
System lock 0.000007
Table lock  0.000061
init    0.000054
optimizing  0.000314
statistics  0.000021
preparing   0.000051
Creating tmp table  0.000084
executing   0.000004
Copying to tmp table    0.000063
optimizing  0.000008
statistics  0.000019
preparing   0.000009
executing   0.000004
Sending data    0.000054
optimizing  0.000008
statistics  0.000007
preparing   0.000009
executing   0.000003
Sending data    0.000126
Sorting result  0.000030
Sending data    0.000025
end 0.000004
removing tmp table  0.000011
end 0.000005
query end   0.000004
freeing items   0.000101
storing result in query cache   0.000008
logging slow query  0.000003
cleaning up 0.000006

Proposed query does

Status  Duration
starting    0.000036
checking query cache for query  0.000122
Opening tables  0.000030
System lock 0.000008
Table lock  0.000064
init    0.000046
optimizing  0.000028
statistics  0.000026
preparing   0.000072
Creating tmp table  0.000086
executing   0.000005
Copying to tmp table    0.001081
Sorting result  0.000040
Sending data    0.000056
end 0.000005
removing tmp table  0.000010
end 0.000005
query end   0.000004
freeing items   0.000108
storing result in query cache   0.000007
logging slow query  0.000003
cleaning up 0.000005

So when you have full data you can try to evalute better the response of both query

Answer 2

You should be able to replace those subqueries by joins:

SELECT one.id
FROM one 
    JOIN `two2` ON (two2.page = 5 AND two2.level = 1)
    JOIN `two` ON ( one.id = two.page AND two.item = two2.item )
    JOIN `three2` ON ( three.item = 5)
    JOIN `three` ON ( one.id = three.item AND three.position > three2.position)
WHERE two.level = 1
ORDER BY three.position