stack stack - 5 months ago 8
SQL Question

How can I make a query force to use such an index?

I have a query like this:

SELECT u.id, user_fname, user_lname, reputation, email, events
FROM users u
INNER JOIN cookies c ON u.id = c.user_id
WHERE c.cookie = $token


Also I have these indexes:


  1. cookies(cookie, user_id)
    -- this is a normal index

  2. cookies(cookie)
    -- this is an unique index



When I execute query above (along with
EXPLAIN
)
, it uses the second index:
cookies(cookie)
. Well how can I make it force to use the first index?
cookies(cookie, user_id)

Answer

If cookie is a unique index, then there is no advantage to using the first index. This is especially true if any of the columns in the SELECT come from the cookies table.

In any case, if the cookie is unique, basically the processing is:

  • Look up the cookie value (really fast, using an index).
  • Look up the corresponding cookie record (really fast, to fetch one page).
  • Look up the corresponding record from users (really fast, using an index).

The first index might be able to eliminate the second fetch. However, the additional information provided by the unique index probably balances this out -- and the query should be really fast either way.