samona Posted July 19, 2010 Share Posted July 19, 2010 Hi, I have the following query in my php code $query = 'Select * From Table_Name as t WHERE t.t_ID = (Select Max(t_ID) From Table_Name Where Type = t.Type and length = t.length) ORDER BY t.Type, t.length'; It takes about 51 seconds to return the results in this query in a table with only 3,000 records. I was wondering if anyone knows a way to make this query more efficient? Link to comment https://forums.phpfreaks.com/topic/208207-sql-sub-query/ Share on other sites More sharing options...
bh Posted July 19, 2010 Share Posted July 19, 2010 Hi, First: Use JOIN Second: Use Indexes. (create index those columns whereat you runs search - in your code: Type and length) Link to comment https://forums.phpfreaks.com/topic/208207-sql-sub-query/#findComment-1088304 Share on other sites More sharing options...
samona Posted July 19, 2010 Author Share Posted July 19, 2010 Thanks!!! Indexing worked nicely. Link to comment https://forums.phpfreaks.com/topic/208207-sql-sub-query/#findComment-1088309 Share on other sites More sharing options...
bh Posted July 19, 2010 Share Posted July 19, 2010 How many seconds now? Link to comment https://forums.phpfreaks.com/topic/208207-sql-sub-query/#findComment-1088315 Share on other sites More sharing options...
samona Posted July 20, 2010 Author Share Posted July 20, 2010 11 seconds, so a 78% improvement. That's great! Link to comment https://forums.phpfreaks.com/topic/208207-sql-sub-query/#findComment-1088527 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.