Hello all. I'm looking for help with the query below. Is there anyway
to prevent the temporary and filesort? I've tried about as many
combinations as I could think of, but can't seem to prevent it. I'm
sure that's the reason, when run on a table of around 750k records, it
takes in excess of 20 seconds. There are indexes on sourceID in both
tables as well as the date field in the first table.
Thanks for any ideas.
SELECT
t1.sourceID as sourceID,
count(t1.sourceID) as clicks,
sum(t1.converted) as conversions,
(sum(t1.converted)/count(t1.sourceID)) * 100 as conv_rate,
count(t1.sourceID) * t2.cost as cost,
sum(t1.revenue) as revenue,
(sum(t1.revenue)) - (count(t1.sourceID) * ifnull(t2.cost,0)) as margin,
( ((sum(t1.revenue)) - (count(t1.sourceID) * t2.cost)) /
sum(t1.revenue) ) * 100 as gm,
(count(t1.sourceID) * t2.cost) / sum(t1.converted) as cpl,
(sum(t1.revenue)) / sum(t1.converted) as rpl,
t2.cost as cpc
FROM source_site_quality as t1
LEFT JOIN rpt_cpc as t2 ON (t1.sourceID = t2.sourceID)
WHERE t1.date >= '2007-06-26' AND t1.date <= '2007-06-28' GROUP BY
sourceID ORDER BY clicks desc, conversions desc;
When using EXPLAIN:
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | t1 | range | idx_date | idx_date |
3 | NULL | 4612 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | t2 | ALL | NULL | NULL |
NULL | NULL | 1351 | |
+----+-------------+-------+-------+---------------+----------+---------+------+------+----------------------------------------------+
-- rich
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
