Welcome to WebmasterWorld Guest from 107.20.108.136

Forum Moderators: open

Message Too Old, No Replies

mysql user variables (in the same statement)

     
4:19 pm on Aug 19, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Jan 18, 2008
posts: 100
votes: 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:

SELECT
blasts.id,
name,
@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,
FROM blasts
ORDER BY
percent

Do I really have to worry about the processing order here? Am I going about this the entire wrong way?
4:24 pm on Aug 19, 2011 (gmt 0)

Moderator from GB 

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:4842
votes: 1


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)

Junior Member

5+ Year Member

joined:Jan 18, 2008
posts: 100
votes: 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

SELECT
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
...
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members