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)
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.
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.
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
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)