MySQL中用户定义的@variable为空

Question

I got problem with following query. When Im running it in mysql workbench, the user defined variable @cid is being filled correctly, however when i run it in PHP the variable is empty. Im using mysqli_query mechanism.

Its complicated query but as I have already said - works fine in mysql WB but not in PHP. In PHP count is being returned correctly so looks like whole query works except of storing of value in @cid variable.

Query:

SELECT count(*),@cid FROM users WHERE yfid IN 
(SELECT viewer_id 
FROM views WHERE campaign_id 
    IN (@cid:=(SELECT * FROM(SELECT campaign_id FROM views 
    where campaign_id>'$firstCampaignId' AND campaign_id 
    IN (SELECT id FROM campaigns WHERE owner_yfid 
    IN (SELECT id FROM uc_users WHERE email='$email')) 
group by campaign_id having count(*) > 1 
ORDER BY count(*) DESC LIMIT 1) AS tmp))) 
AND gender = 1 AND age < 35

EDIT: Ive found out that in MySQL workbench @cid is also NULL when Im running the query for the first time. When Im executing it for the second time, its returned correctly. So probably there is some problem with assigning logic? That is being assigned after excution of query and is accessible on second run?

Answer 1

Try using

select count(*), @cid:=V.campaign_id from
( select yfid from users
  where gender = 1 AND age < 35
) as U
inner join
( select viewer_id, campaign_id FROM views 
  where campaign_id > '$firstCampaignId'
) as V on U.yfid = V.viewer_id
inner join
campaigns as C on V.campaign_id = C.id
inner join
( select id from uc_users where email='$email' ) as UCU on C.owner_yfid = UCU.id
group by V.campaign_id
having count(*) > 1 
order by count(*) desc limit 1

I prefer putting where clauses in subqueries to reduce the number of rows actually joined, though I did not find any information on whether MySQL optimises this already.

It does appear that MySQL optimises join statements but not in clauses.