I'm sending this back to the mailing list where it belongs; that way, other
people can jump in to help and others can learn from the discussion, either
now or via the list archive.
----- Original Message -----
From: "Martin Gallagher" <[EMAIL PROTECTED]>
To: "'Rhino'" <[EMAIL PROTECTED]>
Sent: Saturday, April 08, 2006 6:57 PM
Subject: RE: COUNT() Efficiency
Sorry about the previous empty message.
This is the actual query:
SELECT messages.id, subject, IF(COUNT(messages.id)=1,
CONCAT(members.surname, ', ', members.forename),
CONCAT(COUNT(messages.id),
' Recipients')) AS `to` FROM messages LEFT JOIN members ON
members.id=messages.recipient WHERE author='1' AND messages.flag IN(0, 2,
4)
GROUP BY checksum ORDER BY messages.timestamp DESC LIMIT 0,10
The problem is I can seem to assign COUNT(messages.id) to a MySQL variable
like so:
Do you mean 'can' or 'cannot'? It isn't usually a problem if you _can_
assign a COUNT() expression to a variable....
SELECT messages.id, subject, @count:=COUNT(messages.id), IF(@count =1,
CONCAT(members.surname, ', ', members.forename), CONCAT(@count, '
Recipients')) AS `to` FROM messages LEFT JOIN members ON
members.id=messages.recipient WHERE author='1' AND messages.flag IN(0, 2,
4)
GROUP BY checksum ORDER BY messages.timestamp DESC LIMIT 0,10
This results in NULL for `to`
It might have something to do with the GROUP clause?
How does any of this have anything to do with the efficiency of COUNT()?
You've explained why you need to have the same COUNT() expression in the
SELECT twice and I accept that this looks like it might be reasonable in
this case. But your real problem seems to be the null in the 'to' column,
which has nothing to do with efficiency.
As for your GROUP BY clause, something looks rather odd there; I don't see
checksum in the SELECT at all yet you're grouping by it. Normally, a GROUP
BY names one or more columns from the SELECT that aren't in column functions
so that you can get summarization of the rows returned by the query. This
query doesn't seem to be operating that way. That is not to say that it is
invalid in some way - it might be exactly the right solution to your
problem - but it makes me question what you're doing.
Unfortunately, I'm behind with my own work and don't have the time I would
need to wheedle out enough information to figure out if you are doing the
right thing or the wrong thing. I just jumped in because it appeared that
you had a short simple question; apparently, that is not the case. I'll
leave the others on the list to help you. Be patient, there isn't much
activity on the weekend but things start to pick up once the work week
starts again.
Sorry I can't be more help.
--
Rhino
-----Original Message-----
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: 08 April 2006 15:50
To: Martin Gallagher; mysql@lists.mysql.com
Subject: Re: COUNT() Efficiency
----- Original Message -----
From: "Martin Gallagher" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Saturday, April 08, 2006 6:34 PM
Subject: COUNT() Efficiency
Hi,
If I did a query like:
SELECT COUNT(id) AS count1, COUNT(id) AS count2 FROM table WHERE id<100
Would MySQL run the COUNT() calculation once or twice?
I don't know the answer to your question but why would you want to count
in
the same column of the same table twice in the same query?
I'm not very knowledgeable about MySQL performance - I'm mostly a DB2
guy -
but doesn't MySQL have an Explain command that will tell you what access
path you are getting? If it does, your best bet would be to try your query
and do an Explain to see what it actually does; it should be pretty clear
whether the count() is being done once or twice.
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.0/304 - Release Date: 07/04/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.0/304 - Release Date: 07/04/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.0/304 - Release Date: 07/04/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
