Recipient groups: expensive counts
Closed this issue · 5 comments
I ran into an issue which is probably a non-issue for most use cases. But if you run a big website (around 100.000 entries) and make excessive usage of ENM's possibilities (especially: > 15 recipient groups, all using advanced filters), this may be rather serious!
When the field adds to the datasource ouput, it will also attempt to add a count attribute to each of the recipient groups. Depending on the filters used in each group, the corresponding MySQL query may require several table joins, e.g.:
SELECT SQL_CACHE count(DISTINCT `d`.`value`) as `count`
FROM `sym_entries` AS `e`
LEFT JOIN `sym_entries_data_371` AS t371_1 ON (e.id = t371_1.entry_id)
LEFT JOIN `sym_entries_data_383` AS t383_1 ON (e.id = t383_1.entry_id)
LEFT JOIN `sym_entries_data_512` AS `t512` ON (`e`.`id` = `t512`.entry_id)
LEFT JOIN sym_entries_data_359 AS `d` ON `e`.`id` = `d`.`entry_id`
AND ( t371_1.value IN ('yes') OR t371_1.value IS NULL )
AND (t383_1.value IN ('yes'))
AND (`t512`.relation_id IN ('1') )
AND `d`.`value` IS NOT NULL
These queries may become very expensive and slow. (And the more recipient groups you add, the worse the whole thing will become.)
A a quick fix I commented two lines in field.email_newsletter_manager.php, so I am fine now. (I don't desperately need the counts, although they were "comfortable".)
My question is: What should we do?
- A: Don't do anything. Only crazy people with crazy websites will experience this, and they may apply the above hack.
- B: Think about a configuration option for group counts. (I don't know how to implement this, so I'd probably need help.)
- C: Attempt to make these counts run much more effective. (I have no idea if or how this could be done.)
He, looking at this query I wonder why there is no WHERE. Could it be that this is the issue here?
Wow, indeed, that seems to be the issue. :-)
I will push a fix (which adds WHERE 1 to the query), "as usual".
Ha, I fixed it! @creativedutchmen: The next bug is yours! :-)
Oh shit. Can't believe I missed that one... Thanks!
It must have been like that since version 0.0.1. :-))