Newbie Help with slow query

[sndjnky]

I have a query that takes between 6 - 9 seconds... I know this is because of poor design, but I am in a time crunch and need to figure out why asap....  Below is the query, any help would be very much appreciated!!!!

 

select services.claimno, issues.enduserfname, issues.enduserlname, services.servicetobedone, issues.status, issues.rescode, services.svcenterdate from services, issues where serviceid IN (select max(services.serviceid) from services where services.claimno = issues.claimno) and issues.rep_id="******" and issues.status != 'closed' and issues.status != 'Declined' order by serviceid desc;

 

here is the explain...

 

+----+--------------------+----------+------+---------------+------+---------+------+------+----------------------------------------------+

| id | select_type        | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |

+----+--------------------+----------+------+---------------+------+---------+------+------+----------------------------------------------+

|  1 | PRIMARY            | issues  | ALL  | NULL          | NULL | NULL    | NULL |  65 | Using where; Using temporary; Using filesort |

|  1 | PRIMARY            | services | ALL  | NULL          | NULL | NULL    | NULL |  509 | Using where                                  |

|  2 | DEPENDENT SUBQUERY | services | ALL  | NULL          | NULL | NULL    | NULL |  509 | Using where                                  |

+----+--------------------+----------+------+---------------+------+---------+------+------+----------------------------------------------+

3 rows in set (0.00 sec)

 

 

Thanks

Tom

[aschk]

Seems fairly obvious from your output that NO indexes are being used. Have you specified any indexes on the tables in question? In particular I would look at the columns where JOINs occur and where the WHERE clauses are specified.

[sndjnky]

no indexes yet... I am REALLY REALLY new to the sql programming and started a project that has grown and grown and I think  am a little over my head....  Would you recommendations be to add indexs to both the services and issues tables, for the claimno, serviceid and rep_id columns?

 

Tom

[mwasif]

Then you should first read this pag which will really help you. And this one too http://www.sitepoint.com/article/optimizing-mysql-application.

[fenway]

Or any of the stickes on this board.