What type of index is ideal for this query?

Question

I have an example query such as:

SELECT 
    rest.name, rest.shortname
FROM
    restaurant AS rest
    INNER JOIN specials ON rest.id=specials.restaurantid
WHERE
    specials.dateend >= CURDATE()
AND
    rest.state='VIC'
AND
    rest.status = 1
AND
    specials.status = 1
ORDER BY
    rest.name ASC;

Just wondering of the below two indexes, which would be best on the restaurant table?

id,state,status,name
state,status,name

Just not sure if column used in the join should be included?

Funny enough though, I have created both types for testing and both times MySQL chooses the primary index, which is just id. Why is that?

Explain Output:

1,'SIMPLE','specials','index','NewIndex1\,NewIndex2\,NewIndex3\,NewIndex4','NewIndex4','11',\N,82,'Using where; Using index; Using temporary; Using filesort',
1,'SIMPLE','rest','eq_ref','PRIMARY\,search\,status\,state\,NewIndex1\,NewIndex2\,id-suburb\,NewIndex3\,id-status-name','PRIMARY','4','db_name.specials.restaurantid',1,'Using where'

Not many rows at the moment so perhaps that's why it's choosing PRIMARY!?

Answer 1

For optimum performance, you need at least 2 indexes:

The most important index is the one on the foreign key:

CREATE INDEX specials_rest_fk ON specials(restaurantid);

Without this, your queries will perform poorly, because every row in rest that matches the WHERE conditions will require a full tablescan of specials.

The next index to define would be the one that helps look up the fewest rows of rest given your conditions. Only one index is ever used, so you want to make that index find as few rows from rest as possible.

My guess, state and status:

CREATE INDEX rest_index_1 on rest(state, status);

Your index suggestion of (id, ...) is pointless, because id is unique - adding more column won't help, and in fact would worsen performance if it were used, because the index entries would be larger and you'd get less entries per I/O page read.

But you can gain performance by writing the query better too; if you move the conditions on specials into the join ON condition, you'll gain significant performance, because join conditions are evaluated as the join is made, but where conditions are evaluated on all joined rows, meaning the temporary result set that is filtered by the WHERE clause is much larger and therefore slower.

Change your query to this:

SELECT rest.name, rest.shortname
FROM restaurant AS rest
INNER JOIN specials 
    ON rest.id=specials.restaurantid
    AND specials.dateend >= CURDATE()
    AND specials.status = 1
WHERE rest.state='VIC'
AND rest.status = 1
ORDER BY rest.name;

Note how the conditions on specials are now in the ON clause.