sql sub query

[samona]

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?

[bh]

Hi,

 

First: Use JOIN

Second: Use Indexes. (create index those columns whereat you runs search - in your code: Type and length)

[samona]

Thanks!!! Indexing worked nicely.

[bh]

How many seconds now?

[samona]

11 seconds, so a 78% improvement.  That's great!