CODEWITHSUNDEEP
mysqlsqlindexing
stack
stack

Reputation: 10218

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)

Upvotes: 0

Views: 47

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

use FORCE INDEX (your_index)

http://dev.mysql.com/doc/refman/5.7/en/index-hints.html

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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.

Upvotes: 2

Related Questions