CODEWITHSUNDEEP
mysqlquery-optimization
user1428016
user1428016

Reputation: 117

MySQL takes 10 seconds for counting with conditions on for 100k records

SELECT COUNT(*) AS count_all, products.id AS products_id 
FROM `products` 
INNER JOIN `product_device_facilities` 
  ON `product_device_facilities`.`product_id` = `products`.`id` 
INNER JOIN `product_vendors` 
  ON `product_vendors`.`ProductId` = `products`.`id` 
INNER JOIN `screen_shots` 
  ON `screen_shots`.`ProductVendorId` = `product_vendors`.`id` 
WHERE ( (DownloadCount >= 10 or DownloadCount is NULL) 
  and (Minsdk <= 10 or Minsdk is null)) 
GROUP BY products.id 
HAVING GROUP_CONCAT(device_facility_id ORDER BY device_facility_id ASC ) IN (0)

This is taking 10 seconds for 100k records.

How to improve the performance?

Upvotes: 1

Views: 4466

Answers (3)

Amadeus
Amadeus

Reputation: 189

There are a few things that you can try.

  1. Use persistent connections to the database to avoid connection overhead
  2. Check that all of your tables have Primary keys on the key tables e.g. (product_id)
  3. Use less RAM per row by declaring columns only as large as they need to be to hold the values stored in them. i.e. as @manurajhada said don't use count(*) use count(primary key)
  4. Using simpler permissions when you issue GRANT statements enables MySQL to reduce permission-checking overhead.
  5. use indices on the references between different tables. just remember not to index too many columns, simple rule of thumb, if you never refer to a column in comparisons, there’s no need to index it.
  6. Try using ANALYZE TABLE to help mysql to better optimize the query.
  7. You can speed up a query a tiny bit by making sure all coumns which are not null are declared NOT NULL — thus you speed up table traversing a bit.
  8. Tune MySQL caching: allocate enough memory for the buffer (e.g. SET GLOBAL query_cache_size = 1000000) and define query_cache_min_res_unit depending on average query result set size.
  9. I know it sounds counter intuitive but sometimes it is worth de-normalising tables i.e. duplicate some data in several tables to avoid JOINs which are expensive. You can support data integrity with foreign keys or triggers.

and if all else fails

  1. upgrade your hardware if you can, more RAM, faster HDD can make a significant difference to the speed of the database, and when you have done that allocate more memory to mysql.

EDIT

  • Another option if you do not require the results live as @ask-bjorn-hansen suggested you could use a background task (cron job) once a day, and store the result of the query in a separate table, then in your application all you have to do is check that table for the returned result, that way you get around having to query 100k results and would be able to run queries that take hours and not overly impact your users.

Upvotes: 4

Ask Bj&#248;rn Hansen
Ask Bj&#248;rn Hansen

Reputation: 6943

Are minsdk and download count in the same table? If so adding an index on those two might help.

It could be that it's just a hard/impossible query to do quickly. Without seeing your full schema and the data it's hard to be specific, but it's possible that splitting it up into several easier to execute queries would be faster. Or as Amadeus suggested maybe denormalize the data a bit.

Another variation would be to just live with it taking 10 seconds, but make sure it's always done periodically in the background (with cron or similar) and never while a user is waiting. Then take the time to fix it if/when it takes minutes instead of seconds or otherwise puts an unacceptable burden on your user experience or servers.

Upvotes: 1

manurajhada
manurajhada

Reputation: 5380

Do indexing on join columns on tables and instead of count(*) use count(some indexed primary key column).

Upvotes: 1

Related Questions