Query Optimization question

[cooldude832]

Query looks like
[code]
SELECT urls.UrlID AS UrlID, urls.Url AS Url, urls.Active AS Active, urls.Alert_Time AS Alert_Time, MAX( urls_checks.Date ) AS Last_Check, (

SELECT COUNT( * )
FROM `urls_checks`
WHERE UrlID = urls.UrlID
) AS Check_Count, X.Last_Check, X.http_code AS Last_Code, X.total_time AS Last_Time, AVG( urls_checks.total_time ) AS Avg_Time
FROM `urls`
JOIN urls_checks ON ( urls_checks.UrlID = urls.UrlID )
JOIN (

SELECT UrlID, MAX( Date ) AS Last_Check, http_code, total_time
FROM `urls_checks`
GROUP BY UrlID
) AS X ON urls_checks.UrlID = X.UrlID
AND urls_checks.Date = X.Last_Check
WHERE urls.UserID = '1'
GROUP BY urls.UrlID
LIMIT 0 , 30 

 

and it takes anywhere from 1-3 seconds to run.   

Is the table X what is causing a problem?

I rather not run a seperate query for it but it might save me time

 

Structure is

 

Urls_checks            Urls 

--------         -------------         

UrlCheckID

UrlID      +---------  UrlID

BatchID                  Url                 

Response codes       UserID                     

email [/code]

[fenway]

Let's see the EXPLAIN output.

[cooldude832]

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	<derived3> 	ALL 	NULL 	NULL 	NULL 	NULL 	37 	Using temporary; Using filesort
1 	PRIMARY 	urls_checks 	ALL 	NULL 	NULL 	NULL 	NULL 	45202 	Using where
1 	PRIMARY 	urls 	eq_ref 	PRIMARY 	PRIMARY 	8 	pira00_url.urls_checks.UrlID 	1 	Using where
3 	DERIVED 	urls_checks 	ALL 	NULL 	NULL 	NULL 	NULL 	45202 	Using temporary; Using filesort
2 	DEPENDENT SUBQUERY 	urls_checks 	ALL 	NULL 	NULL 	NULL 	NULL 	45202 	Using where

[fenway]

It's all of the NULL indexes... why not you have any?