CODEWITHSUNDEEP
mysqlsql
AlfaTeK
AlfaTeK

Reputation: 7765

Optimize mysql query: products <- N:N -> categories

Is there any way to do this query in a more optimized way?

select SQL_NO_CACHE count(*) from products p
INNER JOIN `products_categories` AS `pc` ON p.id = pc.products_id
where pc.categories_id = 87

My schema is simple: products, categories and a N:N join table: products_categories. Products are about 400000 rows. products_categories is about 600000. Products with category = 87 are about 18000. Using explain gives:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  pc  index   products_id products_id 8   NULL    612469  Using where; Using index
1   SIMPLE  p   eq_ref  PRIMARY PRIMARY 4   stagingbagthis.pc.products_id   1   Using index

It seems to me that the first line where rows = 612469 is not a very good sign. So, can this query be optimized in any way possible?

Upvotes: 0

Views: 72

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

What if you removed the products table:

select SQL_NO_CACHE count(*)
from products_categories` `pc` 
where pc.categories_id = 87;

For this, you would need an index on products_categories(categories_id), or similar index where categories_id is the first column.

Upvotes: 2

Greg
Greg

Reputation: 2104

You could try this.

select SQL_NO_CACHE count(*) from products p
    INNER JOIN `products_categories` AS `pc` 
        ON p.id = pc.products_id and pc.categories_id = 87

Also check the cardinality of your indexes we have found cases where the cardinality gets out of whack and we need to analyze the tables to get our execution plans back in line.

Upvotes: 0

Barmar
Barmar

Reputation: 780724

You need an index on products_categories.categories_id so that the WHERE clause can be optimized.

Upvotes: 1

Related Questions