CODEWITHSUNDEEP
phpmysql
Quinton Pike
Quinton Pike

Reputation: 3861

MySQL - Does IN() function require full TABLE scan?

Hey Guys, I am doing a social networking site that requires a good bit of queries. I am using 1 query that has a IN() function in it. I am wondering if this requires a full DB scan. I am not sure of any other way to get the same results.

Example Table:

ID | NAME | AGE
05 | Quinton | 25
22 | Stevey | 33
78 | Rebecca | 22
90 | Michael | 26

Query: "SELECT * FROM users WHERE id IN(05,78) ORDER BY id DESC LIMIT 0,2"

Is this the most efficient way to get a list of users that are in a array? (using PHP)

Upvotes: 2

Views: 223

Answers (4)

xkeshav
xkeshav

Reputation: 54050

First of all there is error in Query, it should be

"SELECT * FROM users WHERE id IN(05,78) ORDER BY id DESC LIMIT 0,2"

Second thing, when u r using limit 0,2 then why u scan a full table, u can search like this

WHERE id=05 OR id=78 ORDER BY RAND () LIMIT 2

Upvotes: 0

konsolenfreddy
konsolenfreddy

Reputation: 9671

You can check if an additional WHERE statement speeds things up. As they are evaluated from right to left a limitation to the lowest/highest id might be beneficial e.g.

SELECT * FROM users WHERE id in(13,18,3456) AND id >= 13 AND id <= 3456

(presuming column id is indexed)

Upvotes: 2

Andrew
Andrew

Reputation: 5277

It shouldn't require a db scan if you have an index on the ID field. I am assuming that the ID field is the primary key which would mean it is indexed by default.

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 838816

In general IN does not need a table scan. If you have an index on the id column MySQL will be able to use that index. But when you have only 4 rows in your table it is usually fastest to perform a table scan so when testing on a small amount of data you may find that the index is not being used until you insert more data.

Upvotes: 4

Related Questions