Are these MySQL indexes redundant ?

Consider the following table.



Is there any benefit to having the last two KEY indexes there ?

Would queries that have WHERE clauses dependent on those single columns use the single column INDEX, would they utilize the multi-column INDEX, would they not utilize anything because of the wildcard (*) column selection ?

A few thoughts.

A uniqueness between category_id and item_id pairs is required so that there are no redundant rows. Therefore I need either a PRIMARY or UNIQUE key that uses both of those columns.

I could drop insert_order and use the UNIQUE KEY as the PRIMARY KEY. If I do that though, the ability to have any sort of chronology applied to rows becomes more complicated than I care to think about.

According to the manual, my UNIQUE key inherently provides an index on the category_id column alone, as well as on both columns together because of the "left-most rule".

I could drop the single column INDEX on category_id and rely on the multi-column KEY for the category_id column, but the cardinality of the multi-column key is likely to be close to if not the same as the cardinality of the PRIMARY key, or, the size of the entire table, whereas the cardinality of the single column category_id INDEX should always be smaller since it is limited in size to the number of categories.

Smaller keys mean faster lookups. MySQL appears to favor single column keys when both are present if I understand that manual page right.

Because it is possible to associate an item_id with multiple category_ids, the INDEX on item_id alone makes lookups faster when managing the associations between an item and a category. Because of the "left-most rule", the UNIQUE index in the table can not be used for item_id in situations where I'm looking up the item_id and want to know all categories associated with it.

Ok there's my reasoning.
Am I forgetting anything ?

Well this is certainly interesting.

I've got the following query that uses this relations table when displaying category pages.



Because a category can be assigned as a parent category, I'm fetching the category_id of any category with the current category set as its' parent before this query and that gives me either a "category_id = N" or "category_id IN(N,N)" for "$t->category_sql" depending on how many categories there are. This allows me to have parent categories that both have their own items, and will include the items of their immediate children in their category pages.

The intersting part is that when using EXPLAIN with this query, I see the extra values "Using WHERE; Using Filesort". From what I understand "using filesort" is bad news. There used to be a DISTINCT clause on that query, but that was causing "using temporary" which is bad news as well.

However, when I drop that ORDER BY clause from the query, my extra turns into "Using Index", which means it doesn't even have to look at the data rows, and it still returns the same order. I'm guessing because of that insert_id PRIMARY KEY.

The query also notes both the UNIQUE KEY and the single category_id INDEX as possible keys, it's deciding to use the single column key by the looks of it, though the key_len being 2 in the output for that table has me confused.

You have me confused too!

Nice blog post "Is Our Understanding Backwards", that's pretty trippy, you're probably correct too! haha.

key_len is the length of the index row in bytes, not the number of columns in the index. That's what had me confused.