SELECT
o.*
FROM
`order_product` AS o
WHERE
o.order_id IN (
SELECT id
FROM `order`
WHERE `finish` =0
LIMIT 0,30
)
Ругается
SELECT
o.*
FROM
`order_product` AS o
WHERE
o.order_id IN (
SELECT id
FROM `order`
WHERE `finish` =0
LIMIT 0,30
)
Цитата |
#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' |
SELECT
o.*
FROM
`order_product` AS o
WHERE
o.order_id IN (
select * from (
SELECT id
FROM `order`
WHERE `finish` =0
LIMIT 0,30
) as `id`
)
SELECT
o.*
FROM
`order_product` AS o
WHERE
o.order_id IN (
SELECT id
FROM `order`
WHERE `finish` =0
)
LIMIT 0 , 30
SELECT o. *и будет сравнивать все значения o.order_id с a.id. То есть будет выполнено не сложение, а умножение. Причем внешнего на внутренний. Если в таблице `order_product` 1000 строк, а в `order` 50 то будет выполнено не 30 (если была бы возможность лимита или с еще одним запросом, как у redreem), и даже не 50, как без него, а 50000 "внутренних запросов" в худшем случае. Там немного не так, EXISTS прекращает поиск при первом совпадении, но не известно откуда он начнет считать.
FROM `order_product` AS o
WHERE EXISTS (
SELECT 1
FROM `order` AS a
WHERE a.`finish` =0
AND o.order_id = a.id
)
Цитата (twin @ 18.02.2016 - 00:28) |
А смысл тот же. И механизмы похожи. |
SELECT
o.order_id AS id, o.number, o.print_num, o.board,
o.id_product, o.size, o.color, p.pol,
ko.date, ko.name,
ac.print , ac.text
FROM
`order_product` AS o
LEFT JOIN
`product` AS p ON p.id = o.id_product
LEFT JOIN
`order` AS ko ON ko.id = o.order_id
LEFT JOIN
`order_comment` AS ac ON ac.id_order = o.order_id
WHERE
o.live = 0 AND
o.order_id IN (
SELECT * FROM
(
SELECT o2.id
FROM `order` AS o2
LEFT JOIN `order_comment` AS ac2 ON ac2.id_order = o2.id
WHERE o2.finish = 0 AND ac2.print >= 1 AND ac2.print < 3
ORDER BY o2.id DESC
LIMIT $c,$str_index
) AS `id`
)
ORDER BY
o.order_id DESC,
ac.print,
ko.date DESC
SELECTи
o.id, o.date, o.name, o.city,
ac.print , ac.text, ac.date_out
FROM
`order` AS o
LEFT JOIN
`order_comment` AS ac ON ac.id_order = o.id
WHERE
o.finish = 0 AND
ac.print >= 1 AND
ac.print < 3
ORDER BY
o.id DESC
LIMIT
$c,$str_index
SELECT
o.order_id AS id, o.number, o.print_num, o.board,
o.id_product, o.size, o.color, p.pol
FROM
`order_product` AS o
LEFT JOIN
`product` AS p ON p.id = o.id_product
WHERE
o.live = 0 AND
o.order_id IN (".implode(',', $select).")