need to speed up my database or query somehow

I hav开发者_运维百科e a left join statement on 2 tables: tbl1 has about 1 million records and tbl2 has about 20 million. The query takes about 9 seconds to execute. I have indexes on both but something doesn't seem right given how long queries take. Here is my select statement:

EXPLAIN SELECT * FROM (SELECT tbl1.id, tbl1.name, tbl2.addr FROM tbl1 LEFT JOIN tbl2 ON tbl2.id = tbl1.id WHERE tbl1.name LIKE 'G%' AND tble2.addr IS NOT NULL) as tempTable GROUP BY id LIMIT 10;

+----+-------------+------------+--------+------------------+--------------+---------+----------------| 
id|select_type| table     |type  |poss_keys|key |key_len|ref         | rows | Extra 
+----+-------------+------------+--------+------------------+--------------+---------+----------------
1 | PRIMARY   | <derived2>|system| NULL    |NULL| NULL  |NULL        |    0 | const row not found |
2 | DERIVED   | tbl1      |range |id,name  |name| 903   |NULL        |    1 | Using where         |
2 | DERIVED   | tbl2      |ref   |id,addr  |id  | 4     |tbl2.tbl1.id|   25 | Using where         |
+----+-------------+------------+--------+------------------+--------------+---------+----------------

UPDATE: If I take out the join statement & just select records from tbl1 or from tbl2 individually the select statements are fast (well under 1 second) but when I try to join them then it slows it down onsiderably...am I joining them correctly or do I need some kind of special index for joins?

You might simplify it a bit... I don't think you need a temp table or a group...

  SELECT tbl1.id, tbl1.name, tbl2.addr 
FROM tbl1 LEFT JOIN tbl2 ON tbl2.id = tbl1.id 
WHERE tbl1.name LIKE 'G%' AND tble2.addr IS NOT NULL

Also...if the index doesn't seem to be working you could add "tble2.id like 'G%'"

You can try many other methods

Try using

mysql_unbuffered_query 

on php side that will make it faster i think as you have specified limit as 10 no more taking count will be needed

Then as result contains only 10 results

SQL_SMALL_RESULT

If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table. so making small result query's faster