SQL magic - query shouldn't take 15 hours, but it does

Answer 1

Ok, so 900K rows isn't a massive table, it's reasonably big but and your queries really shouldn't be taking that long.

First things first, which of the 3 statements above is taking the most time?

The first problem I see is with your first query. Your WHERE clause doesn't include an indexed column. So this means that it has to do a full table scan on the entire table.

Create an index on the "data_updated" column, then run the query again and see what that does for you.

If you don't need the hash's and are only using them to avail of the dark magic then remove them completely.

Edit: Someone with more SQL-fu than me will probably reduce your whole set of logic into one SQL statement without the use of the temporary tables.

Edit: My SQL is a little rusty, but are you joining twice in the third SQL staement? Maybe it won't make a difference but shouldn't it be :

SELECT temp1.element_id, 
   temp1.category, 
   temp1.source_prefix, 
   temp1.source_name, 
   temp1.date_updated, 
   AVG(temp1.value) AS avg_value,
   SUM(temp1.value * temp1.weight) / SUM(weight) AS rating
FROM temp1 LEFT JOIN temp2 ON temp1.subcat_hash = temp2.subcat_hash
WHERE temp1.date_updated = temp2.maxdate
GROUP BY temp1.cat_hash;

or

SELECT temp1.element_id, 
   temp1.category, 
   temp1.source_prefix, 
   temp1.source_name, 
   temp1.date_updated, 
   AVG(temp1.value) AS avg_value,
   SUM(temp1.value * temp1.weight) / SUM(weight) AS rating
FROM temp1 temp2
WHERE temp2.subcat_hash = temp1.subcat_hash
AND temp1.date_updated = temp2.maxdate
GROUP BY temp1.cat_hash;

Answer 2

Using hashses is one of the ways in which a database engine can execute a join. It should be very rare that you'd have to write your own hash-based join; this certainly doesn't look like one of them, with a 900k rows table with some aggregates.

Based on your comment, this query might do what you are looking for:

SELECT cur.source_prefix, 
       cur.source_name, 
       cur.category, 
       cur.element_id,
       MAX(cur.date_updated) AS DateUpdated, 
       AVG(cur.value) AS AvgValue,
       SUM(cur.value * cur.weight) / SUM(cur.weight) AS Rating
FROM eev0 cur
LEFT JOIN eev0 next
    ON next.date_updated < '2009-05-01'
    AND next.source_prefix = cur.source_prefix 
    AND next.source_name = cur.source_name
    AND next.element_id = cur.element_id
    AND next.date_updated > cur.date_updated
WHERE cur.date_updated < '2009-05-01'
AND next.category IS NULL
GROUP BY cur.source_prefix, cur.source_name, 
    cur.category, cur.element_id

The GROUP BY performs the calculations per source+category+element.

The JOIN is there to filter out old entries. It looks for later entries, and then the WHERE statement filters out the rows for which a later entry exists. A join like this benefits from an index on (source_prefix, source_name, element_id, date_updated).

There are many ways of filtering out old entries, but this one tends to perform resonably well.