MySQL Forums
Forum List  »  Performance

select over big tables
Posted by: Rico Proft
Date: January 12, 2005 12:57AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
select over big tables
2903
January 12, 2005 12:57AM
1773
January 21, 2005 08:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.