MySQL Forums
Forum List  »  Performance

performance on select query
Posted by: lucy smiles
Date: May 27, 2005 10:35AM

hi all,
this is a classic problem and may have been solved on some post but i couldn't get any answer on google search.

we have a table that stores people information like first name, last name address etc. and the auto generated id is in relation with another table. which holds rest of the data.

table1 -> table2
id -> id

when we are doing a look up query sorts lname,fname alphabetically and displays first 20 and then next 20.

our query

select lname,fname,address,accountno from tbl1,tbl2 where tbl1.id = tbl2.id and lname like '%' and fname like '%' order by lname,fname limit 20,20

all the necessary columns are indexed.

if we have 200k records and run this query it takes 5-8 secs for result set to return.

for eg, if we have

select lname,fname,address,accountno from tbl1,tbl2 where tbl1.id = tbl2.id and lname like 'A%' and fname like '%' order by lname,fname limit 20,20

it takes less than one second. but second query defeats the purpose of look up.

we have 2 version of mysql 4.0.18 and 4.1.10 and both on windows/linux.

can some body please give tips on how to optimize the first query ?

thank you
lucy.

Options: ReplyQuote


Subject
Views
Written By
Posted
performance on select query
2404
May 27, 2005 10:35AM
1445
May 27, 2005 02:18PM
1453
May 29, 2005 06:48PM


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.