CODEWITHSUNDEEP
mysqlsqldatabaseperformanceindexing
user3586822
user3586822

Reputation: 1

Efficient indexes for lookup table

I'm trying to understand the proper way to assign indexes on a lookup table. Given the following tables and sample query, what are the most efficient primary/additional indexes for the lookup table?

Table: items (id, title, etc.)

Table: categories (id, title, etc.)

Table: lookup (category_id, item_id, type, etc.)

SELECT * FROM items 
    INNER JOIN lookup ON
        lookup.item_id=items.id AND lookup.type="items" 
    INNER JOIN categories ON 
        categories.id=lookup.category_id;

Upvotes: 0

Views: 1262

Answers (3)

Rick James
Rick James

Reputation: 142528

Do not have an auto_incr id for the mapping table.

Have

 PRIMARY KEY(type, item_id, category_id),
 INDEX(category_id, type, item_id)

For the second index, will you need type when going from a category to an item? If not, leave it out.

More: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Upvotes: 0

The Impaler
The Impaler

Reputation: 48865

Apart from the join predicates your query only has a single filering precate (lookup.type = "items"). If this predicate has a good selectivity (i.e. it selects 5% or less of the rows) then you should use it as the first column of the index. I would do:

create index ix1 on lookup (type, item_id, category_id)

If the id columns on the table items and categories represent the primary keys, then there's nothing else to do.

The engine will probably read the lookup table using the index, and then will read the other two tables using their PK indexes.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

For this query:

SELECT *
FROM items i JOIN
     lookup l
     ON l.item_id = i.id AND l.type = 'items' JOIN 
     categories c
     ON c.id = l.category_id;

The best indexes are probably:

  • lookup(type, item_id)
  • categories(id) (probably there already if id is a primary key)
  • items(id) (probably there already if id is a primary key)

Under some circumstances, this may not be a big improvement, particularly if most lookup() rows have a type of "items".

Upvotes: 1

Related Questions