PowerKiKi
commented
8 years ago For future reference, I think you're referring to #65 where we discovered that InnoDB is more performant than MyISAM.
Could you please give the result of this SQL query to have an idea of how much data we are talking about ?
SELECT * FROM
(SELECT COUNT(*) AS bounceaccount FROM tx_newsletter_domain_model_bounceaccount) AS bounceaccount,
(SELECT COUNT(*) AS email FROM tx_newsletter_domain_model_email) AS email,
(SELECT COUNT(*) AS link FROM tx_newsletter_domain_model_link) AS link,
(SELECT COUNT(*) AS linkopened FROM tx_newsletter_domain_model_linkopened) AS linkopened,
(SELECT COUNT(*) AS newsletter FROM tx_newsletter_domain_model_newsletter) AS newsletter,
(SELECT COUNT(*) AS recipientlist FROM tx_newsletter_domain_model_recipientlist) AS recipientlist;I guess storing the MD5 hash in DB would be an easy way to solve this performance issue, but I need to be sure there is no better way before starting to work on that. So I would need to reproduce your behavior locally for a detailed analysis.
If we do so, it would speed up email opening, link opening and bounce handling. So that might be a good idea indeed...
githubrsys
Hi, we already discussed the SQL performance a while ago.
Especially the update statements on opening a newsletter is really bad. I just tracked down under heavy load:
Original update statement changed to a select
Select * from tx_newsletter_domain_model_email WHERE MD5(CONCAT(uid, recipient_address)) = '0e5c449fa5c377005f6d3349dbc451eb'
--> Query took 3.6151 seconds
Alternative statement changed to a select
Select * from tx_newsletter_domain_model_email WHERE newsletter = '1124' and recipient_address = 'foo@bar.de'
Query took 0.0214 seconds
I did not examine the whole thing with the possible need of the md5 hash. So my question is: Do you see any chance to avoid the hashing to get a tremendous better performance?!
Cheers David