• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

MYSQL query not using multiple column index

I have a multiple column index on a mysql table. Let's say the index is (first, second, third). When I run this query

SELECT first, second, third
FROM table

EXPLAIN shows me it is using the index. But if I run the following query

SELECT first, second, third, fourth
FROM table

it is not using the index.

Do I need all the columns that I am selecting to be in the index?
0
spectrumcare
Asked:
spectrumcare
  • 3
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
No but there is no reason to use the index to just SELECT fields.  If you use ORDER BY the indexed fields, then it should use the index.
0
 
spectrumcareAuthor Commented:
Sorry, I am using  order by FIRST. But for whatever reason, it is not using the index if I try to query a field that is not included in the index. I've even tried forcing the index.
0
 
spectrumcareAuthor Commented:
Could the issue be that I have an autonumber column that is forced to have a primary index on it?
0
 
spectrumcareAuthor Commented:
I've got it working now. I'll give you the points for answering.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now