@Tarique
I'm working on a project where some table rowcount went from 1 to 15 millions in one year
The same queries which were taking 2 min to run, suddenly ( really suddenly, i mean the next day ) were taking 45 min
Some of those queries were queries with nested NOT IN in it.
From what i learnt: the sql engine, depending on the size of the table, choose or not to use the index of the table when running subqueries.
When you reach a certain point ( i'm not dba so i won't go into more details ), the engine decides not to use the index anymore, but to do a table scan --> that's when you start to get in trouble.
To solve that and get back (lot) faster running queries, depending on the need, we did one of the two following things:
- replaced queries of the kind
select * from a where a.id not in ( select id from b where etc...)
with
select b.id into #tmptable_b where etc.. ( syntax in mysql being "select into temporary table tmptable_b i think..)
select * from a where a.id not in ( select id from #temptable_b )
OR ( and i think that example fits more w/ cpg )
- replaced the same query
select * from a where a.id not in ( ... ) and a.another_column = 'another_value"
with
select * from a where a.another_column = 'another_value'
then in the script/program,
after fetching the results
(pseudo coding here )
forbidden_set = list( 4, 7, 9, 12) //list recovered by sql also
if ( forbidden_set.contains( mysql_result[$i.['aid']] )
then
skip //NOT IN replacement
else
process it
Those are just some thoughts
but even if the numbers are differents..i think the concept stays the same
-matt-