slow query when I used ORDER BY

Question

When I trying query with order by it works very slow. It takes 20-30seconds. Without order by about 1 second. Mysql version 5.5

SELECT distinct(product.id),
      product.position,
                   product.condition,
                   product.city_id,
                   product.price,
                   product.company_name,
                   product.timestamp_update,
                   product.company_id,
                   company.company_type,
                   company.image as company_image,
                   city.name as city_name
    FROM `product_to_tag` `v2t`
    JOIN `product` ON product.id = v2t.product_id
    JOIN `product_to_city` `vtc` ON product.id = vtc.product_id
    JOIN `city` `c` ON c.id = vtc.city_id
    LEFT JOIN `city` `city` ON city.id=product.city_id
    LEFT JOIN `company` ON company.id=product.company_id
    WHERE ((`product`.`publish` = 1)) AND (product.id !=  5016460) AND (c.id = 99 or c.parent_id = 99) AND ((`v2t`.`tag_id` IN (65, 181, 228, 1135)))
    ORDER BY `product`.`timestamp_update` DESC LIMIT 30;

When you say it runs in 1 second originally, do you mean it takes 1 second before it starts returning data? Or that it takes 1 second to *complete*? — Phylogenesis, Sep 12 '17 at 15:15
Possible duplicate of [MYSQL, very slow order by](https://stackoverflow.com/questions/14368211/mysql-very-slow-order-by) — Phylogenesis, Sep 12 '17 at 15:20
When you have a limit of 30, then MySQL can return the first 30 results it receives. When you have an `order by` clause, it needs to get all the results, then sort them, then return the top 30 items. The latter obviously takes much longer. — Phylogenesis, Sep 12 '17 at 15:48

Ryan Gadsdon · Answer 1 · 2017-09-12T15:51:33.640

Is there any indexes on your order by column?

It is most likely because if your query does not contain an order by then it will return the data in whatever order it was found. This is obviously the quickest option. Whereas, when you include an order by clause, the database has to build a list of the rows in the correct order and then return the data in that order.

This clearly is more time consuming on the database and most likely why it takes longer. If you have indexes on the ordered column, only other suggestion would be to look at your order of joining tables e.g joining smallest table first.

slow query when I used ORDER BY

1 Answers1