MySQL - How to optimize my query

Question

I'm so bad in making good MySQL queries. I've created this one:

SELECT SQL_CALC_FOUND_ROWS
  `products_stock`.`products_id`,
  `products_stock`.`products_stock_attributes`,
  `products_stock`.`products_stock_quantity`,
  `products`.`manufacturers_id`,
  `products_description`.`products_name`
FROM `products_stock` 
  LEFT JOIN  `products`
    ON  `products_stock`.`products_id` = `products`.`products_id` 
  LEFT JOIN  `products_description`
    ON  `products_stock`.`products_id` = `products_description`.`products_id` 
  LEFT JOIN  `products_to_categories`
    ON `products_stock`.`products_id` = `products_to_categories`.`products_id` 
WHERE `products_stock`.`products_stock_quantity` >=3
  AND `products`.`products_status` = 1
  AND ISNULL(`products`.`products_image`) = false
  AND `products`.`products_image` != ""
  AND EXISTS(
   select * from `allegro`
   where `products_stock`.`products_id` = `allegro`.`product_id` 
     and `allegro`.`attributes` =  `products_stock`.`products_stock_attributes`
  ) = false

products Table have about 17k rows, allegro Table have about 3k of rows.

The query Idea is select all products, where stock_quanity > 3, where is photo, and where is no product id in allegro table.

Now the query takes about 10 seconds. I have no idea how I can optimize it.

@SOLVED

I've added indexes to allegro.product_id and allegro.attributes and now query tooks less than half of second. Thanks all for help

If the issue is solved, simply accept the appropriate answer. If the solution isn't in any of the provided answers, feel free to write an answer yourself and accept that. — MvG
– MvG, Commented Jul 11, 2012 at 11:05

MvG · Accepted Answer · 2012-07-11 09:33:28Z

2

Get rid of the EXISTS ( SELECT … ) = FALSE stuff.

Turn that into something along these lines:

LEFT JOIN allegro
          ON products_stock.products_id = allegro.product_id
         AND allegro.attributes = products_stock.products_stock_attributes
…
WHERE allegro.product_id IS NULL

See the Rewriting Subqueries as Joins chapter in the MySQL docs.

Further suggestions:

Don't join with tables you don't use, like products_to_categories
Instead of ISNULL(…) = FALSE write … IS NOT NULL

edited Jul 11, 2012 at 9:33

answered Jul 11, 2012 at 9:20

MvG

61.8k25 gold badges158 silver badges294 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

PiKey Over a year ago

I've converted all tables to InnoDB, I have removed EXISTS() stuff and added LEFT JOIN allegro as you suggest. I have removed ISNULL(...) and used IS NOT NULL. Query is slow as before, maybe faster by 1 second.

MvG Over a year ago

Please post updates EXPLAIN output. Also what number of result lines will the query yield? If that is substantially less than 17k, then identify the condition in your query which excludes most of the ommitted products, and make sure that there is an index to provide that decision quickly.

PiKey Over a year ago

WOW, I have added index to allegro.product_id and allegro.attributes and now query tooks less than 1 second! Thanks all for help. I have no idea that indexes are so important. Thanks thanks thanks

MvG Over a year ago

@PiKey, note that usually a query can use only one index per table, but an index can span multiple columns. So as you join allegro using both of the named columns, you probably should combine them into a single index, at least for this query. And do you have a key for products_id in all the other tables?

vkGunasekaran · Accepted Answer · 2012-07-11 09:49:44Z

0

Before trying following methods, check your tables engine set to innoDB and it is indexed properly, then you can try straight join. It will force the look into the left table first.

Another method is using memcache for slow queries. You can cache the query result into an array by using this method, that avoids querying db frequently. cached results can be updated after specified period of time.

edited Jul 11, 2012 at 9:49

answered Jul 11, 2012 at 9:36

vkGunasekaran

6,8247 gold badges52 silver badges60 bronze badges

Collectives™ on Stack Overflow

MySQL - How to optimize my query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related