CODEWITHSUNDEEP
mysqlindexing
Kevin
Kevin

Reputation: 1725

Query not using an index

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

Answers (1)

mvp
mvp

Reputation: 116437

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

Related Questions