Reputation: 1725
I am running a query against 2 tables and it's not using the index for some reason. I can't figure it our for the life of me.
Here are my tables: table1
and table2
. Table 1 is a list of categories with 154 rows. The table structure for table 1 looks like this:
category_id (int11) - also has an index on it
name (varchar30) - also has an index on it
urltext (text)
nametext (text)
category_id name urltext nametext
1 category1 blahblah blahblah
2 category2 blahblah blahblah
3 category3 blahblah blahblah
4 category4 blahblah blahblah
etc to rows
154 category154 blahblah blahblah
Table 2 has 1+ million rows, and relates products categories. The table field structure looks like this:
row_id (bigint11) - also has an index
category_id (int3) - also has an index
product_id (int9) - also has an index
The data in table 2 looks like this:
row_id category_id product_id
1 1 123456
2 4 123456
3 17 456789
4 42 456789
5 7 456789
6 88 888555
Here's my query:
select *
from table2
INNER JOIN table1
ON table2.category_id=table1.category_id
where table2.product_id = 123456;
And now, when I run an explain on this query:
id select_type table type possible_keys key key_len ref rows Extra
1 simple table1 ALL PRIMARY NULL NULL NULL 154
1 simple table2 ref product_id,... poduct_id 10 category_id, const 1 Using Where
Any product usually only relates to between 1 and 4 different categories. So, you'd think that if the index was being used correctly, instead of seeing 154 rows for table1 in the explain results, you'd see a max of 4.
I originally had table1.name set as text
instead of varchar(30)
, but still the results have not changed. This wouldn't matter so much if this were a randomly used query, but this query is getting hit many tens of thousands of times daily.
What am I doing wrong here that this query is not using the available indexes to massively reduce the number of rows?
I hope I provided the correct information. Thanks in advance.
Upvotes: 0
Views: 70
Reputation: 116407
Your query can be reformatted as:
SELECT *
FROM table2
INNER JOIN table1 ON table2.category_id = table1.category_id
WHERE table2.product_id = 123456
To make it fast, following indexes should exist:
table1(category_id)
to make lookup by table1.category_id
fast.
And
table2(category_id, product_id)
or
table2(product_id, category_id)
This multi-column (compound) index will work to satisfy both JOIN
condition and WHERE
condition.
Note that having separate indexes on product_id
and category_id
is not enough. But, if you have compound index on (category_id, product_id)
, you can delete index on (category_id)
, unless it was unique index (like primary key).
Upvotes: 1