Re: indexe questions

[tata668]

Wow, that is a complete answer Jay Pipes, thanks.

If I understand correctly what you say, the "other1/id" index is not used but *could be*, Am I right? My fear was that because of the "AND test1.id IN (", the index would never be used..

You are also right about test1 and test2 containing really few values because my application is still in development so there are only few values yet.


"First off, you can get rid of your dependent subquery (which isn't needed at all and would be much more efficient as a join)"


Problem is I don't write the query in a straithforward way, I don't write it in "one shot". I use a lot of OO (with PHP) and there is a object called "Filter" that take charge of adding some restrictions to the query.
It's easier to make the "Filter" object output all those restrictions in a "WHERE" clause:

" AND id IN (SELECT xxx) "

Ouputing some "INNER JOIN" would be tricky because I couldn't do this after the "WHERE", I would have to break the construction of the query even more.

Anyway, I'm not sure if I'm clear (my english is not so good..) but thanks a bunch! I'll analyze your comment more deeply and maybe I'll be back with some more questions if you don't mind! :-)