MySQL: Order of indices in multi-column index

Databases
1

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').

2

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

3

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 ) ;

4

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.

5

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

6

Fantastic, thanks!

it can be

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

7

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