performance on select query
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.