Listing items of a particular category

Question

I have the following query which brings back a list of items which fall into a particular category:

SELECT i.id, ir.range_name, it.item_type, i.item_value
FROM items i
LEFT JOIN item_types it ON it.id = i.item_type
LEFT JOIN item_ranges ir ON ir.id = i.item_name
LEFT JOIN menu m ON m.id = it.menu_link
WHERE m.menu_title =  'vehicles'
AND i.item_live = 1
ORDER BY ir.range_name

I was wondering whether the m.menu_title column needs to be indexed. Currently, there are only about 10 rows in the menu (m) table and this is unlikely to change that much.

However, with the joins, the above query will potentially bring back over 1000000 rows. There are foreign keys on all the joins.

I am trying to avoid filling the database with dummy data to test the query.

Answer 1

Yes, No, and Maybe.

Yes

The table has 10 rows, the column is used as a constant argument in a query, and the query optimizer has ways to do index lookups (probes) in a very fast way. So, if the query parser decides to access the menu table by the literal, instead of the id, then the index will likely help. Also, for 10 rows, it will not take much space.

No

Because you use a left-join from items to menu, on the menu.id column, the reality is that the query plan will likely have to process all the records in item before joining to menu so that it can do the outer-join right. This means that all the query access to the menu table should be through the id column, and the menu_title column will be secondary, and the index on the menu_title (if any) will be ignored.

Maybe

Query optimizers can sometimes make dumb, and also very smart choices, and there is a chance that having the index will help, despite my best-guess that it wont. Additionally, there is a chance the optimizer will use the index, even though that will make it slower.

There is no way to know unless you try it.

My suggestion

Create the index, but don't expect it to be used for this query.

A second suggestion is to create the index menu(id, menu_title) because sometimes putting them together in an index helps avoid a data page lookup, but for just 10 rows, it's not going to make much difference.

Answer 2

If you require menu_title = 'vehicles', then that should be written as an inner join, not a left join — for clarity and possibly a tiny performance boost.