MySQL: Order of indices in multi-column index

Let’s say you have two columns in a MySQL index: a and b. Is there any advantage to having b first and a second if b has a higher cardinality? If b has a higher cardinality presumably MySQL can narrow down the results faster. Is that correct? Assume that if b and a and swapped round the queries will be adjusted (e.g. WHERE b = 'foo' AND a = 'bar' instead of WHERE a = 'bar' AND b = 'foo').

more or less, yes

if the index is { a , b } then

  • searching with WHERE b=value will ignore the index

  • searching with WHERE a=value might use the index, depending on statistics

note: statistics > cardinality

my favourite example is the relationship (“junction”) table

first of all, it should never have its own auto-increment PK, but instead –

CREATE TABLE book_authors ( book_id INTEGER NOT NULL , auth_id INTEGER NOT NULL , PRIMARY KEY ( book_id, auth_id ) , CONSTRAINT author_book FOREIGN KEY ( book_id ) REFERENCES books ( id ) , CONSTRAINT book_author FOREIGN KEY ( auth_id ) REFERENCES authors ( id ) );

note that declaring a PK implies an index will be created for it

then, if you want to display a given book, you’ll need

SELECT ... FROM books WHERE id=value

and to show the authors of that book (always a good idea) you’ll need to extend the join through the relationship table –

SELECT ... FROM books INNER JOIN book_authors ON book_authors.book_id = books.id INNER JOIN authors ON authors.id = book_authors.auth_id WHERE books.id=value

the PK index { book_id , auth_id } will definitely be used for this

what about searching for all the books by a given author?

in that case, the query will start at the authors table, then search the book_authors table for rows matching that particular author

thus, you’d also want to have –

ALTER TABLE book_authors ADD INDEX author_books ( auth_id , book_id ) ;

Thanks for this. I think you’ve sort of answered what my next question was going to be with your example above. I was going to ask if the order you specify in WHERE matters if you include all of them — but it seems that it does else you wouldn’t have needed the second index per above. I thought if you have index(a, b, c) and you did WHERE a = value1 AND c = value3 AND b = value 2 the index would still work; it would effectively just rearrange it to WHERE a = value1 AND b = value2 AND c = value 3. If I read your post correctly then the order does matter: it should always be left-to-right.

A couple of further questions:

  1. Is it considered bad practice to have an auto-increment PK on a table that already has a unique index? I tend to always use an auto-increment PK as that tends to be used by the CMS, if not on the front-end site. Does having the extra index in this context only slow down when you’re inserting and updating?

  2. Is there much of a performance difference between a single VARCHAR (30) unique column and a combined index of 3 x VARCHAR(10) (So long as they’re queried in the right order)? I’m not talking about big tables: 50k rows max.

it does not, and i’m sorry if i gave that impression (although i don’t see how i did)

if you do not specify a value for the leftmost column in the index, then the index is ignored

it can be

yes

i suspect it’s not very measurable

Fantastic, thanks!

it can be

I think my use of an auto inc int PK for CMS is reasonable use then.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.