A table structure

I have a开发者_JAVA百科 table called table1. It has 3 columns:

id, int, auto incrementing, primary key
id2,int
description,varchar(255)

The only key that is set is id as primary key.

From this I run this query:

SELECT * FROM table1 WHERE id2=1 OR 5 OR 2 LIMIT 250;

I have not set any other keys other than id as primary key and there are about 20 possibilities for id2 values (all integers) and three are selected randomly for each query.

The table is currently MYISAM.

What can I do to make the query above faster?

What other keys should I set on table1?

Would combining id and id2 together in a key make it faster?

It seems that you are hinting at the answer already. Since you are doing searches on the id2 column in the where clause of your statement, then an index on that column will speed up these quesries.

You should create an index on table1 (id2) and rewrite the query so that it selects only the records holding the values you provide:

SELECT  *
FROM    table1
WHERE   id2 IN (1, 2, 5)
LIMIT 250

As it is now, the query is like this:

SELECT  *
FROM    table1
WHERE   (id2  = 1) OR 2 OR 5
LIMIT 250

, with 2 and 5 being evaluated as boolean TRUE.

This leads to all records (within the limit) being returned, not regarding their actual values.

You can turn id2 into an index.

ALTER TABLE  `table1` ADD INDEX (  `id2` );

You can also make the query a bit better by restructuring it a bit

SELECT * FROM `table1` WHERE `id2` IN (1,2,5) LIMIT 250;

Your query is wrong, you'll retrieve the entire dataset and then limit it to 250 results. Reason is wrong WHERE clause.

WHERE id2 = 1 matches that specific row, however "OR 5 OR 2" won't match the ID column, they'll simply evaluate as true. So you're selecting everything.

What you can do is use

SELECT * FROM table1 WHERE id2 = 1 OR id2 = 5 OR id = 2

OR

SELECT * FROM table1 WHERE id2 IN(1, 5, 2)

Also, even tho id2 has low selectivity, you should index it. Reason is: you have description column, that makes the data file larger than possible index file. It's still faster to scan the index file entirely than the data file. You'll have a small benefit, but it will be a benefit.

You should set an index on id2. The index on id doesn't matter unless you are specifically querying for that, and a multi-column index of id,id2 would not be useful at all. Also, you can change your query to:

SELECT * FROM table1 WHERE id2 in (1,2,5) LIMIT 250;