Index Question

[azzazzello]

Hiya,

I am seeing some weirdness on my DB. I was working on a project 3-4 months ago, and tweaked a query and indexes to work pretty fast (subsecond). Now I come back to it, and the query stalls...There are 6k more records in it than before (for a total of 13k). Here's a problem. My query is like this

SELECT FROM t1,t2,t3 WHERE (...) AND t1.my_id NOT IN (SELECT my_id from t1 WHERE (...) )

I have to use subquery. Here's the thing - separately each query takes a fraction of a second, but when I use a NOT IN, it stalls. I tried doing this

CREATE INDEX my_id_index on t1(my_id);

(which is what I did last time and it helped tremendously! Have no idea where it went)

Perhaps I should also mention that this is all being done on a slave in a replication setup, and that the master does not have any of these indexes.

Any idea?

 

[azzazzello]

Ok, I fixed it. But still have no idea where the heck the proper index went