2 sql identity queries w 'diff params - only one requires a temporary table

Question

The only difference in these sql queries is the record_id parameter (as I look at the entire result set). Tables are myisam. The first query runs well, and the second is very slow. Any idea why this would be?

This query works fine

explain select r.record_id, r.oai_datestamp, r.format_id, r.status, x.xml, max(u.date_updated) as date_updated from marcnormalization.records r, marcnormalization.records_xml x, marcnormalization.record_updates u where r.record_id = x.record_id and (r.record_id > 1802000 or 1802000 is null) and r.record_id = u.record_id and (u.date_updated > '1960-10-19 10:18:52.0' or '1960-10-19 10:18:52.0' is null) and u.date_updated <= '2010-10-07 10:18:52.0' group by u.record_id order by u.record_id limit 1000; 

this query is very slow (creates a temporary table)

 explain select r.record_id, r.oai_datestamp, r.format_id, r.status, x.xml, max(u.date_updated) as date_updated from marcnormalization.records r, marcnormalization.records_xml x, marcnormalization.record_updates u where r.record_id = x.record_id and (r.record_id > 2202000 or 2202000 is null) and r.record_id = u.record_id and (u.date_updated > '1960-10-19 10:18:52.0' or '1960-10-19 10:18:52.0' is null) and u.date_updated <= '2010-10-07 10:18:52.0' group by u.record_id order by u.record_id limit 1000; 

update: I went through my problem by changing

 group by u.record_id order by u.record_id 

to

 group by r.record_id order by r.record_id 

So, now this is a moot point, but I'm still interested to know the initial question.

Answer 1

I think this case is also related to your area of ​​sorting connections. You can increase this for a session. try like this:

mysql> select @@ max_heap_table_size;

mysql> SET SESSION max_heap_table_size = 19777216;

then run the query.

Baris Akverdi

Answer 2

What about:

 EXPLAIN SELECT r.record_id, r.oai_datestamp, r.format_id, r.status, x.xml, max(u.date_updated) as date_updated FROM marcnormalization.records_xml x INNER JOIN (SELECT * FROM marcnormalization.records WHERE record_id > 1802000) r ON r.record_id = x.record_id INNER JOIN (SELECT * FROM marcnormalization.record_updates WHERE date_updated BETWEEN '1960-10-19 10:18:52.0' AND '2010-10-07 10:18:52.0') u ON r.record_id = u.record_id group by u.record_id ASC limit 1000; 

I think it could be faster?