I have the following prob. I need to make a select over two big tables (tab1 = 1.5 Millionen records and tab2 with 4.8 Millionen records)
I have a couple of indexes for example: tab1->id_so, tab1->name, tab1->state, tab2->id_code, tab2->code
here my query:
SELECT * from tab1 ,tab2 WHERE tab1.id_so = tab2.id_so and ( tab2.id_code =2819 or tab2.id_code =2813 or tab2.id_code =5321 or tab2.id_code =5329 ) and ( tab1.state ="ACT" or tab1.state ="NSW" or tab1.state ="NT" or tab1.state ="QLD" or tab1.state ="TAS" or tab1.state ="VIC" or tab1.state ="WA" ) and tab2.code ="A" order by tab1.name
the query needs always over 180 sec :( .
i tried the same without or and with in but its still the same: over 180 sec :(
SELECT * from tab1 ,tab2 WHERE tab1.id_so = tab2.id_so and ( tab2.id_code IN(2819,2813,5321,5329 )) and ( tab1.state IN ("ACT","NSW","NT","QLD","TAS","VIC" )) and tab2.code ="A" order by tab1.name
can anyone help me to make this faster? does mysql use my index in this case?
Thanks in advance,
Rico.
http://galaxyinternet.com.au
Edited 3 time(s). Last edit at 11/10/2005 06:00PM by Rico Proft.