|mysql user variables (in the same statement)|
| 4:19 pm on Aug 19, 2011 (gmt 0)|
According to mysql's documentation, you're not supposed to use user variables (@var_name) in the same statement because of undefined processing order of the variables.
See: [dev.mysql.com ]
I've done a query that does this. It seems ok, but I'm worried. Basically I'm pulling back a list of email blasts and for each one I'm showing number of emails sent, email opened, and opened percentage.
To obtain the value for emails sent and emails opened i have do a subquery for each. then i do the math on those two results to obtain the percentage. I also want to be able to order by any of these three values. This means I have to store the results for the subqueries per row so i can create a percentage field.
So here is an example of my statement:
@sent := (SELECT COUNT(*) FROM emails WHERE emails.blast_id = blasts.id AND status = 'sent') AS sent,
@opened := (SELECT COUNT(*) FROM emails WHERE emails.blast_id = blasts.id AND status = 'sent' AND opened = '1') AS opened,
(@opened / @sent * 100) AS percent,
Do I really have to worry about the processing order here? Am I going about this the entire wrong way?
|brotherhood of LAN|
| 4:24 pm on Aug 19, 2011 (gmt 0)|
You could alternatively LEFT JOIN on emails, use COUNT(*) to get the equivalent @sent,@opened values and GROUP BY blasts.id
Ordering on any row will be fine.
| 6:33 pm on Aug 19, 2011 (gmt 0)|
Thanks for your suggestion. I did a few tests and found this method is the fastest and provides the best caching results over the user variable method and doing subqueries instead of the join.
The only downside is I still have to repeat the bits of logic in the calculation for percentage, but its not that big of a deal with the help of PHP
COUNT(IF(emails.status = 'sent', 1, NULL)) AS sent,
COUNT(IF(emails.opened = '1', 1, NULL)) AS opened,
(COUNT(IF(emails.opened = '1', 1, NULL)) / COUNT(IF(emails.status = 'sent', 1, NULL)) * 100) AS PERCENT