sndjnky Posted March 28, 2008 Share Posted March 28, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/98335-newbie-help-with-slow-query/ Share on other sites More sharing options...
aschk Posted March 28, 2008 Share Posted March 28, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/98335-newbie-help-with-slow-query/#findComment-503226 Share on other sites More sharing options...
sndjnky Posted March 28, 2008 Author Share Posted March 28, 2008 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 Quote Link to comment https://forums.phpfreaks.com/topic/98335-newbie-help-with-slow-query/#findComment-503241 Share on other sites More sharing options...
mwasif Posted March 28, 2008 Share Posted March 28, 2008 Then you should first read this pag which will really help you. And this one too http://www.sitepoint.com/article/optimizing-mysql-application. Quote Link to comment https://forums.phpfreaks.com/topic/98335-newbie-help-with-slow-query/#findComment-503256 Share on other sites More sharing options...
fenway Posted March 28, 2008 Share Posted March 28, 2008 Or any of the stickes on this board. Quote Link to comment https://forums.phpfreaks.com/topic/98335-newbie-help-with-slow-query/#findComment-503623 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.