sql select slow with type IN question

lottos

I'm seeing a very significant difference in time to get results between these two queries. The first one takes significantly longer and the only fundemental difference is the "type IN"

SELECT count(t.theid) FROM tableA t WHERE t.fid IN (1,2,3) AND t.theid NOT IN (SELECT theid FROM tableB WHERE username='$user' AND type IN('y', 'z')) AND (TO_DAYS(trndate) - TO_DAYS(CURDATE()) < 11)

SELECT count(t.theid) FROM tableA t WHERE t.fid IN (1,2,3) AND t.theid NOT IN (SELECT theid FROM tableB WHERE username='$user' AND type='z') AND (TO_DAYS(trndate) - TO_DAYS(CURDATE()) < 11)

Can anyone elaborate on why this would be?
tableB has approx 1 million rows of data and tableA approx 7 thousand rows of data.
Or a variation/more efficient way to run the sql statement?

Piranha

I can't explain why it is like that, only that I have noticed the same thing myself. One way to make the query faster is to avoid the keyword IN if possible. Your query would then be something like this:

SELECT count(t.theid) FROM tableA t WHERE (t.fid = 1 or t.fid = 2 or t.fid = 3) AND t.theid NOT IN (SELECT theid FROM tableB WHERE username='$user' AND (type = 'y' or type = 'z')) AND (TO_DAYS(trndate) - TO_DAYS(CURDATE()) < 11)

It is possible that proper indexing will solve the problem with IN, I simply don't know.

laserlight

I can't explain why it is like that, only that I have noticed the same thing myself.

That is weird though: I began to prefer IN to OR for SQLite after reading about OR not being able to to take advantage of indices (or some other deficiency like that) in the SQLite mailing list some time back, though if I remember correctly this has been rectified more recently.

It is possible that proper indexing will solve the problem with IN, I simply don't know.

Yes, is there an index on type?

EDIT:
Weirder still: I tested on MySQL5 with a table with half a million rows of randomly generated integers in the range [1,1000]. A simple query with IN is faster than that with OR, at least when I arbitrarily picked a handful (3 to 6) of integers to test with. Of course, when an index is applied everything is blazingly fast (nearly 0 seconds for both), though MySQL still reports the IN version as being slightly faster (0.00 seconds compared to 0.01 to 0.02 seconds for OR).

Now, lottos' comparison is between a query that has IN and a query that does not have any such comparison at all. It is possible that substituting the IN with an OR will result in a query that fares no better, or is even slower, so suggesting such a switch is not necessarily correct.