MySQL ORDER BY is making the query slow...

iceomnia

Hi Guys,

I have the following query:

SELECT
	i.iditems,
	MIN(d.price) AS price,
	MAX(d.price)AS pricefrom,
	COUNT(d.iddeals) AS deals,
	i.name,
	i.summary
FROM
	items AS i
INNER JOIN
	deals AS d
	ON d.iditems = i.iditems
GROUP BY
	(i.iditems)
ORDER BY
	deals DESC
LIMIT
	10;

This query take 4 seconds to run on a very fast dedicated server with no other server load.

There are 4.5 million rows in the dals table and 2.2M in the items table.

When I remove this line:

ORDER BY
	deals DESC

It goes from 4 seconds to 0.0023 seconds.

What can I do to optimise the order by as I am ordering on a COUNT() column?

Bjom

Just a wild guess and for experimenting: You could try wrapping the count in a subselect. If you are lucky this improves performance - could be, that it has no effect or gets worse either....

SELECT iditems, price, pricefrom, deals FROM

(SELECT
i.iditems,
MIN(d.price) AS price,
MAX(d.price)AS pricefrom,
COUNT(d.iddeals) AS deals,
i.name,
i.summary
FROM
items AS i
INNER JOIN
deals AS d
ON d.iditems = i.iditems
GROUP BY
(i.iditems)
) AS subs
ORDER BY
deals DESC
LIMIT
10;

iceomnia

Thanks for the input.

No - unfortunately it makes the query 5.992 seconds.

🙁

Any other ideas?

There must be a way to "pre-index" the count or something?

Bjom

Temporary table?

CREATE TABLE...SELECT

CREATE TEMPORARY TABLE `tMyTmp` (
`iditems` varchar(45) NOT NULL,
`price` varchar(45) NOT NULL,
`pricefrom` varchar(45) NOT NULL,
`deals` varchar(45) NOT NULL,
PRIMARY KEY USING BTREE (`iditems`)
UNIQUE KEY `deals` USING BTREE (`ixDeals`)
 ) ENGINE=MEMORY
SELECT
	i.iditems,
	MIN(d.price) AS price,
	MAX(d.price)AS pricefrom,
	COUNT(d.iddeals) AS deals,
	i.name,
	i.summary
FROM
	items AS i
INNER JOIN
	deals AS d
	ON d.iditems = i.iditems
GROUP BY
	(i.iditems);

dagon

which fields are indexed?