Hello,
well as a rule of thumb that is true Paul, but depends on the case of
course. Usually the JOINs are faster and sub-selects might really kill
performance but I am more an expert on MySQL then on dbmail ...
Including the data structure of dbmail since I started using it about
2 weeks ago.
cheers,
Daniel Urstöger
Paul J Stevens schrieb:
Then why do I keep hearing noises on this list that JOINs are faster
than sub-SELECTS ??
Michael Monnerie wrote:
Hello, I found this message in my log, the query was run from
dbmail-util:
dbmail/maintenance[10985]: Warning:[db] dbmodule.c,db_query(+151):
slow query [SELECT p.id FROM dbmail_physmessage p LEFT JOIN
dbmail_headervalue h ON p.id = h.physmessage_id WHERE h.physmessage_id
IS NULL] took [74] seconds
I made explain analyze SELECT p.id FROM dbmail_physmessage p LEFT JOIN
dbmail_headervalue h ON p.id = h.physmessage_id WHERE h.physmessage_id
IS NULL;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Left Join (cost=576001.98..642824.08 rows=116125 width=8)
(actual time=167715.689..231489.237 rows=15 loops=1)
Merge Cond: ("outer".id = "inner".physmessage_id)
Filter: ("inner".physmessage_id IS NULL)
-> Index Scan using dbmail_physmessage_pkey on dbmail_physmessage
p (cost=0.00..3987.60 rows=116125 width=8) (actual
time=0.089..1689.135 rows=115520 loops=1)
-> Sort (cost=576001.98..586426.01 rows=4169613 width=8) (actual
time=161547.086..200170.129 rows=4167058 loops=1)
Sort Key: h.physmessage_id
-> Seq Scan on dbmail_headervalue h (cost=0.00..117522.13
rows=4169613 width=8) (actual time=0.445..70881.046 rows=4167058 loops=1)
Total runtime: 231785.972 ms
(8 Zeilen)
And I think this query should do the same (it at least finds the same
15 rows for me), but is considerably faster:
EXPLAIN ANALYZE SELECT p.id from dbmail_physmessage p where p.id NOT
IN (select physmessage_id from dbmail_headervalue);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on dbmail_physmessage p (cost=127947.85..130367.41
rows=58062 width=8) (actual time=105060.736..105533.860 rows=15 loops=1)
Filter: (NOT (hashed subplan))
SubPlan
-> Seq Scan on dbmail_headervalue (cost=0.00..117523.68
rows=4169668 width=8) (actual time=1.405..75893.989 rows=4167100 loops=1)
Total runtime: 105546.817 ms
(5 Zeilen)
What do you think?
mfg zmi
------------------------------------------------------------------------
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
