Good to see the issue has been solved. What I noticed in the mysqltuner output,
is that you may want to enlarge your table_cache and open files limit before
you run into problems there.
- Original Message -
From: Johnny Withers joh...@pixelated.net
I haven't used MYISAM in a long
/
any thoughts or help would be appricated.
thanks
On Mon, Oct 3, 2011 at 1:22 PM, Eric Bergen eric.ber...@gmail.com wrote:
Can you run show processlist in another connection while the select
count(*) query is running and say what the state column is?
On Mon, Oct 3, 2011 at 7:00 AM, Joey L mjh2
/
any thoughts or help would be appricated.
thanks
On Mon, Oct 3, 2011 at 1:22 PM, Eric Bergen eric.ber...@gmail.com wrote:
Can you run show processlist in another connection while the select
count(*) query is running and say what the state column is?
On Mon, Oct 3, 2011 at 7:00 AM, Joey L mjh2
with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
any thoughts or help would be appricated.
thanks
On Mon, Oct 3, 2011 at 1:22 PM, Eric Bergen eric.ber...@gmail.com wrote:
Can you run show processlist in another connection while the select
count(*) query is running
be ignored.
#
!includedir /etc/mysql/conf.d/
any thoughts or help would be appricated.
thanks
On Mon, Oct 3, 2011 at 1:22 PM, Eric Bergen eric.ber...@gmail.com wrote:
Can you run show processlist in another connection while the select
count(*) query is running and say what the state column
...@gmail.com wrote:
Can you run show processlist in another connection while the select
count(*) query is running and say what the state column is?
On Mon, Oct 3, 2011 at 7:00 AM, Joey L mjh2...@gmail.com wrote:
this is not a real query on the site - it is just a way i am measuring
performance on mysql
I keep finding it extremely peculiar that a count(*) on a MyISAM table would
take that long. InnoDB needs to effectively *count* the records, but MyISAM
keeps accurate statistics and can just read it from the metadata.
This suggests to me that not all your metadata (ie., table descriptors et
thanks for the response - but do not believe queries are the issue
because - Like I said - i have other websites doing the same exact
queries as I am doing on the site with the 9gig table.
Contrary to popular believe, size DOES matter... And having a table large
enough so it doesn't fit in
here is mysqlreport ---
root@rider:~/tmp# ./mysqlreport --user root --password barakobomb
Use of uninitialized value $is in multiplication (*) at ./mysqlreport line 829.
Use of uninitialized value in formline at ./mysqlreport line 1227.
MySQL
Joey, does your 'large' table get
On Thu, Oct 6, 2011 at 3:22 PM, Joey L mjh2...@gmail.com wrote:
here is mysqlreport ---
root@rider:~/tmp# ./mysqlreport --user root --password barakobomb
Use of uninitialized value $is in multiplication (*) at
Sorry, hit send by accident there! *face palm*
Just had a quick scan of the report. You've got 2 1GB disks in software raid
- RAID1 or RAID5? I can also see you're creating a lot of temporary files on
disk. I think in your previous email that your biggest table's index(s) were
larger then the
i did google search - myisam is faster...i am not really doing any
transaction stuff.
thanks
On Thu, Oct 6, 2011 at 12:16 PM, Andrew Moore eroomy...@gmail.com wrote:
Sorry, hit send by accident there! *face palm*
Just had a quick scan of the report. You've got 2 1GB disks in software raid
-
I am curious.. Are you the only client on this database or or there other
connections doing work in the background? A busy insert/update heavy
application could cause these effects.
- michael dykman
On Thu, Oct 6, 2011 at 12:35 PM, Joey L mjh2...@gmail.com wrote:
i did google search -
...@singerwang.comwrote:
Okay, lets hold on for a minute here and go back. We're side tracking too
much.
Lets state the facts here:
1) MyISAM stores the row count internally, a 'select count(*) from table'
DOES NOT DO A FULL TABLE SCAN
2) hell, a software RAID6 of 2 MFM drives could do a seek
PM, Singer X.J. Wang w...@singerwang.com
wrote:
Okay, lets hold on for a minute here and go back. We're side tracking too
much.
Lets state the facts here:
1) MyISAM stores the row count internally, a 'select count(*) from table'
DOES NOT DO A FULL TABLE SCAN
2) hell, a software RAID6 of 2
I've sent this email a few times now, mysql list kept rejecting it due to
size, sorry for any duplicates
I think you need to examine this query in particular:
| 2567 | p_092211 | localhost | p_092211 | Query | 11 | Sending
data | select oldurl, newurl, id, dateadd from
From: Joey L mjh2...@gmail.com
i did google search - myisam is faster...i am not really doing any
transaction stuff.
That's true for read-only. But if you have a mix of reads and writes, MYISAM
locks tables during writes, which could be blocking reads.
In a museum in
Guys - I wanted to thank you all very much for your help
I found the offending code on the website !
thank you very very very much...
what did it for me was a combination of show processlist and show full
processlist.
I saw the full queries and the main thing was that it was doing a
query
Glad you got to the bottom of it Joey.
On 7 Oct 2011 01:23, Joey L mjh2...@gmail.com wrote:
Guys - I wanted to thank you all very much for your help
I found the offending code on the website !
thank you very very very much...
what did it for me was a combination of show processlist and
Thanks for the input -
1. I will wait 48 hours and see what happens.
2. can you tell me what are some performance tests I can do to help me
better tune my server ?
3. I am concerned about this table : | w6h8a_sh404sef_urls |
MyISAM | 10 | Dynamic| 8908402 |174
have you tried
select count(yourindex) instead of select count(*) ?
On Mon, Oct 3, 2011 at 7:53 AM, Joey L mjh2...@gmail.com wrote:
Thanks for the input -
1. I will wait 48 hours and see what happens.
2. can you tell me what are some performance tests I can do to help me
better tune my
:58 AM, Andrés Tello mr.crip...@gmail.com wrote:
have you tried
select count(yourindex) instead of select count(*) ?
On Mon, Oct 3, 2011 at 7:53 AM, Joey L mjh2...@gmail.com wrote:
Thanks for the input -
1. I will wait 48 hours and see what happens.
2. can you tell me what are some
Can you run show processlist in another connection while the select
count(*) query is running and say what the state column is?
On Mon, Oct 3, 2011 at 7:00 AM, Joey L mjh2...@gmail.com wrote:
this is not a real query on the site - it is just a way i am measuring
performance on mysql - I do
I have having issues with mysql db - I am doing a select count(*) from
table -- and it take 3 to 4 min.
My table has about 9,000,000 records in it.
I have noticed issues on my web pages so that is why i did this test.
I have about 4 gig of memory on the server.
Is there anything I can do to fix
Is your table MyISAM or InnoDB?
A
On Sun, Oct 2, 2011 at 2:44 PM, Joey L mjh2...@gmail.com wrote:
I have having issues with mysql db - I am doing a select count(*) from
table -- and it take 3 to 4 min.
My table has about 9,000,000 records in it.
I have noticed issues on my web pages so
?
A
On Sun, Oct 2, 2011 at 2:44 PM, Joey L mjh2...@gmail.com wrote:
I have having issues with mysql db - I am doing a select count(*) from
table -- and it take 3 to 4 min.
My table has about 9,000,000 records in it.
I have noticed issues on my web pages so that is why i did this test.
I have about 4
I'd suggest mysqltuner. You can get it by using:
wget http://mysqltuner.pl
See what suggestions that makes
On 10/02/2011 06:44 AM, Joey L wrote:
I have having issues with mysql db - I am doing a select count(*) from
table -- and it take 3 to 4 min.
My table has about 9,000,000 records
L mjh2...@gmail.com wrote:
I have having issues with mysql db - I am doing a select count(*) from
table -- and it take 3 to 4 min.
My table has about 9,000,000 records in it.
I have noticed issues on my web pages so that is why i did this test.
I have about 4 gig of memory on the server
The section called: Variables to adjust: --when it says -- does this
mean I have to set it higher in my.cnf file ?? and if I have a -- does
this mean I have to set it lower ??
thanks...here is the info below you both asked for :
mysql select count(*) from w6h8a_sh404sef_urls
this mean I have to set it lower ??
thanks...here is the info below you both asked for :
mysql select count(*) from w6h8a_sh404sef_urls ;
+--+
| count(*) |
+--+
| 8908193 |
+--+
1 row in set (2 min 5.53 sec)
| w6h8a_session | MyISAM | 10
The meaning is:
increase max_connections
reduce wait_timeout
-- 28800 is wait 8 hours before closing out dead connections
same for interactive_timeout
increase key_buffer_size ( 7.8G) increase join_buffer_size
-- This keeps mysql from having to run to disk constantly for keys
-- Key buffer
On Fri, Jul 9, 2010 at 9:09 PM, Andrés Tello mr.crip...@gmail.com wrote:
Hi, I'm using mysql 4.1.21, a legacy system. Next step to migrate it, but
for now, I need the community help.
I have a 23GB table, if I do a select count(over_an_index) from table it
uses 1.4minutes to read. The main
As usual, after you send a mail, you check for other things
From the 32GB of ram, I only was allocating 2G, not by the process, but by
all the S.O...
free -G reported barely 1 gig of ram...
XD - happy face that I have lots of ram...
now my face is like: ¬¬ - why I didn't verfy the amount of
Hi, I'm using mysql 4.1.21, a legacy system. Next step to migrate it, but
for now, I need the community help.
I have a 23GB table, if I do a select count(over_an_index) from table it
uses 1.4minutes to read. The main issue is that this table is the main table
of a system and each query is taking
)
left JOIN sf_threads ON sf_forums.id = sf_threads.forumidfk)
left JOIN sf_messages ON sf_threads.id = sf_messages.threadidfk
GROUP BY
sf_conferences.id,
sf_conferences.name
--
View this message in context:
http://www.nabble.com/select-count%28%29.-Help-a-newb-tp16582786p16582786.html
Sent from
Hi I think you would do this :
SELECT
sf_conferences.id,
sf_conferences.name,
count(*) AS `count`
FROM
((
LEFT JOIN sf_forums ON sf_conferences.id=sf_forums.conferenceidfk)
LEFT sf_threads ON sf_forums.id = sf_threads.forumidfk)
LEFT JOIN sf_messages ON sf_threads.id = sf_messages.threadidfk
= sf_messages.threadidfk
GROUP BY
sf_conferences.id,
sf_conferences.name
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
View this message in context:
http://www.nabble.com/select-count%28%29
, a.first_name, a.last_name,b.(SELECT COUNT(*) AS
count
FROM login_table b WHERE a.username = b.username) FROM user_list a
Try ...
SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count
FROM user_list a
JOIN login_table b ON a.username = b.username
GROUP BY a.username,a.first_name
the list of users I would do :
SELECT username, first_name, last_name FROM user_list
And to count the number of connections I would do
SELECT COUNT(*) AS count FROM login_table WHERE username =
$result['username']
Can I do something like this :
SELECT a.username, a.first_name, a.last_name,b
Richard,
Can I do something like this :
SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count
FROM login_table b WHERE a.username = b.username) FROM user_list a
Try ...
SELECT a.username, a.first_name, a.last_name,COUNT(b.username) AS count
FROM user_list a
JOIN login_table
(because of the group by ...).
Thankyou
Peter Brawley a écrit :
Richard,
Can I do something like this :
SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS count
FROM login_table b WHERE a.username = b.username) FROM user_list a
Try ...
SELECT a.username, a.first_name
Sorry it's me again, I made a mistake, it counts the number of logins
correctly, but does not show members with 0 logins !
Any idea how to do this?
Thanks :)
Peter Brawley a écrit :
Richard,
Can I do something like this :
SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT
To
[EMAIL PROTECTED], mysql@lists.mysql.com
cc
Subject
Re: group a select * and a select COUNT from 2 different tables using
result of first table to do the COUNT ... is it possible ?
Sorry it's me again, I made a mistake, it counts the number of logins
correctly, but does not show members
: group a select * and a select COUNT from 2 different tables using
result of first table to do the COUNT ... is it possible ?
Sorry it's me again, I made a mistake, it counts the number of logins
correctly, but does not show members with 0 logins !
Any idea how to do this?
Thanks :)
Peter
idea how to do this?
Thanks :)
Peter Brawley a écrit :
Richard,
Can I do something like this :
SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS
count
FROM login_table b WHERE a.username = b.username) FROM user_list a
Try ...
SELECT a.username, a.first_name, a.last_name,COUNT
not on the liste and if the user
has logged in more than once the result is 1 (because of the group by
...).
Thankyou
Peter Brawley a écrit :
Richard,
Can I do something like this :
SELECT a.username, a.first_name, a.last_name,b.(SELECT COUNT(*) AS
count
FROM login_table b WHERE a.username
Hi,
My task is to limit calculation of total number of items in the database
that satisfy certain conditions. I join two tables using WHERE and there are
millions of records as the result. When I do SELECT count(*) it takes
really too long. The table has appropriate indexes and I
tables using WHERE and there are
millions of records as the result. When I do SELECT count(*) it takes
really too long. The table has appropriate indexes and I experimented with
replacing the conditions, etc., so I don't think there is a way to make it
work any faster. In my case it would
The problem is that there are certain conditions after WHERE different for
each query and the results number can be very different.
--
View this message in context:
http://www.nabble.com/Applying-LIMIT-to-SELECT-count%28*%29-tp14453544p14459808.html
Sent from the MySQL - General mailing list
:
http://www.nabble.com/Applying-LIMIT-to-SELECT-count%28*%29-tp14453544p14459808.html
Sent from the MySQL - General mailing list archive at Nabble.com.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED
Alex Arul wrote:
count(*) is slow in innodb due to Multi Versioning. Which table type
are you using ?
Hi Alex,
Thanks for answering; All tables are MyISAMs. I'm beginning to wonder if
I am not taxed heavily for using floats for the geographic coordinates.
--
MySQL General Mailing List
on a 1.8GB 42mil
records table:
SELECT COUNT(*) FROM geoRecord WHERE geoRecord.geolatitude IS NOT NULL ;
As it took forever to complete (I mean more than 20 minutes) I've
stopped the client and attempted to work around.
The table looks like this:
EXPLAIN geoRecord
:
SELECT COUNT(*) FROM geoRecord WHERE geoRecord.geolatitude IS NOT NULL ;
As it took forever to complete (I mean more than 20 minutes) I've
stopped the client and attempted to work around.
The table looks like this:
EXPLAIN geoRecord
Hi everybody,
MySQL 5.0.21 running on RedHat EL4, 2GHz CPU, 2,5GB RAM, RAID5/128MB
RAM. At one point I had to issue the following query on a 1.8GB 42mil
records table:
SELECT COUNT(*) FROM geoRecord WHERE geoRecord.geolatitude IS NOT NULL ;
As it took forever to complete (I mean more than
Hello,
I would like merge this 3 query into a single one...
SELECT COUNT(*) AS count FROM orders WHERE order_status = 'pending',
SELECT COUNT(*) AS count FROM orders WHERE order_status = 'processing',
SELECT COUNT(*) AS count FROM orders WHERE order_status = 'shipped',
I could do :
SELECT
|
++---+
3 rows in set (0.00 sec)
Dan
Hello,
I would like merge this 3 query into a single one...
SELECT COUNT(*) AS count FROM orders WHERE order_status = 'pending',
SELECT COUNT(*) AS count FROM orders WHERE order_status = 'processing',
SELECT COUNT(*) AS count FROM orders
On 11/1/05, Kevin Burton [EMAIL PROTECTED] wrote:
MyISAM has a cool feature where it keeps track of the internal row
count so that
SELECT COUNT(*) FROM FOO executes in constant time. Usually 1ms or so.
The same query on INNODB is O(logN) since it uses the btree to
satisfy the query.
I
faster?
Rhino
- Original Message -
From: sheeri kritzer [EMAIL PROTECTED]
To: Kevin Burton [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Monday, November 14, 2005 5:10 PM
Subject: Re: Will SELECT COUNT(*) FROM TABLE ever be cheap on INNODB?
Hi all,
I know I'm a bit late in coming
[EMAIL PROTECTED] wrote:
Jigal van Hemert [EMAIL PROTECTED] wrote on 11/02/2005 03:29:14 AM:
If I understand
http://dev.mysql.com/doc/refman/5.0/en/table-and-index.html
correctly, the index of the primary key is stored as the clustered index
together with the data. To me this means that
Shankar Unni wrote:
[EMAIL PROTECTED] wrote:
If I understand the InnoDB engine correctly, I don't see how they
could speed it up unless they start tracking how many records belong
to each active version within a database.
But one thing you can do to speed it up somewhat is to do a
Jigal van Hemert [EMAIL PROTECTED] wrote on 11/02/2005 03:29:14 AM:
Shankar Unni wrote:
[EMAIL PROTECTED] wrote:
If I understand the InnoDB engine correctly, I don't see how they
could speed it up unless they start tracking how many records belong
to each active version within a
, November 02, 2005 10:30 AM
Subject: Re: Will SELECT COUNT(*) FROM TABLE ever be cheap on INNODB?
Shankar Unni wrote:
[EMAIL PROTECTED] wrote:
If I understand the InnoDB engine correctly, I don't see how they
could speed it up unless they start tracking how many records belong
to each active version
MyISAM has a cool feature where it keeps track of the internal row
count so that
SELECT COUNT(*) FROM FOO executes in constant time. Usually 1ms or so.
The same query on INNODB is O(logN) since it uses the btree to
satisfy the query.
I believe that MyISAM just increments an internal
At 12:39 -0800 11/1/05, Kevin Burton wrote:
MyISAM has a cool feature where it keeps track of the internal row
count so that
SELECT COUNT(*) FROM FOO executes in constant time. Usually 1ms or so.
The same query on INNODB is O(logN) since it uses the btree to
satisfy the query.
I believe
Kevin Burton [EMAIL PROTECTED] wrote on 11/01/2005 03:39:59 PM:
MyISAM has a cool feature where it keeps track of the internal row
count so that
SELECT COUNT(*) FROM FOO executes in constant time. Usually 1ms or so.
The same query on INNODB is O(logN) since it uses the btree
On 11/1/05, Kevin Burton wrote:
MyISAM has a cool feature where it keeps track of the internal row
count so that
SELECT COUNT(*) FROM FOO executes in constant time. Usually 1ms or so.
The same query on INNODB is O(logN) since it uses the btree to
satisfy the query.
Are you sure? Finding
Are you sure? Finding a single record using an index may be O(logN),
but wouldn't reading all of the index be O(N)?
Yeah.. you're right. It would be O(N)... I was thinking this as I
hit the send button :)
Kevin
Kevin A. Burton, Location - San Francisco, CA
AIM/YIM - sfburtonator,
[EMAIL PROTECTED] wrote:
If I understand the InnoDB engine correctly, I don't see how they could
speed it up unless they start tracking how many records belong to each
active version within a database.
But one thing you can do to speed it up somewhat is to do a
COUNT(PK_column) (rather
Hi all
I was reading the High Performance MySQL book (by O'Reilly) and there
was mentioned that executing a count(*) is slower on the InnoDB
engine compared to the MyISAM engine, because InnoDB tables do not keep
track of the number of the records. I just wanted to know that if
this performance
/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hi Behrang,
ORACLE :
**
There are two ways to have count(*) in oracle :
statistics not updated :
SQL select count(*) from titi;
COUNT
Mathias,
Thanks a lot!
I will not explain the same thing for sqlserver, sybase ..., but when
your RDBMs
have a data dictionnary, you don't need to execute count(*) :o) WITH
Updated
statistics of course.
I'm a little bit confused here. Why the count(*) is not transformed to a
select from
Selon Behrang Saeedzadeh [EMAIL PROTECTED]:
Mathias,
Thanks a lot!
I will not explain the same thing for sqlserver, sybase ..., but when
your RDBMs
have a data dictionnary, you don't need to execute count(*) :o) WITH
Updated
statistics of course.
I'm a little bit confused here.
Mathias [EMAIL PROTECTED] wrote on 06/28/2005 06:13:08 AM:
Selon Behrang Saeedzadeh [EMAIL PROTECTED]:
Mathias,
Thanks a lot!
I will not explain the same thing for sqlserver, sybase ..., but
when
your RDBMs
have a data dictionnary, you don't need to execute count(*) :o)
Selon [EMAIL PROTECTED]:
Mathias [EMAIL PROTECTED] wrote on 06/28/2005 06:13:08 AM:
Selon Behrang Saeedzadeh [EMAIL PROTECTED]:
Mathias,
Thanks a lot!
I will not explain the same thing for sqlserver, sybase ..., but
when
your RDBMs
have a data dictionnary, you don't
it's quite faster.
Hope that helps
:o)
Mathias
And I was trying to explain why there is not a number IN the data
dictionary that represents row count. Unless a separate dictionary is
maintained FOR EACH TRANSACTION, the record counts will be wrong. The
record counts determined by SELECT
. The
record counts determined by SELECT COUNT(*) are *per transaction* so the
only way to do a record count is by checking each row (pending or not)
against cross-transaction isolation.
This has everything to do with the row-level locking built into InnoDB and
unless they enhance the engine
select count(*) table; gives me a syntax error in 4.0 and 4.1 so I'm not
sure how that worked. Something similar would be select count(*) t;
which uses the shortcut alias syntax. It's the same as doing select
count(*) as t;
Simon Garner wrote:
[EMAIL PROTECTED] wrote:
I have a curious
just give a it try select count(columnname) from table;
Eric Bergen [EMAIL PROTECTED] wrote:select count(*) table; gives me a syntax
error in 4.0 and 4.1 so I'm not
sure how that worked. Something similar would be select count(*) t;
which uses the shortcut alias syntax. It's the same as doing
I have a curious situation I was hoping someone could shed some light on.
mysql select count(*) table;
+---+
| table |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
mysql select count(*) from table;
+--+
| count(*) |
+--+
|25965 |
+--+
1 row in set (0.00 sec
[EMAIL PROTECTED] wrote:
I have a curious situation I was hoping someone could shed some light on.
mysql select count(*) table;
+---+
| table |
+---+
| 0 |
+---+
1 row in set (0.00 sec)
mysql select count(*) from table;
+--+
| count(*) |
+--+
|25965
Jerry Swanson wrote:
[...]
I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from account where
[snip]
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from
have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from account
2005 22:51:00 -0500, Dave Merrill [EMAIL PROTECTED] wrote:
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count
[snip]
This is what I was looking for. Why the query is call cross-tab?
[/snip]
It is called a crosstab because of the way the reults resemble a results
table. Here is an article that shows the crosstab in larger use
http://www.evolt.org/article/Using_MySQL_and_PHP_to_Present_Excel_Spread
[snip]
What query suppose to be faster? Jan or Dave?
[/snip]
Queries using equalities are generally faster than LIKE. YMMV.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from account where status='cancelled';
Is it possible to get count of each
-0500, Dave Merrill [EMAIL PROTECTED]
wrote:
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from
and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from account where status='cancelled';
Is it possible to get count of each
[snip]
What is the difference between '='' and ''?
[/snip]
= EQUAL TO
NOT EQUAL TO
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
I would think that the Jan solution should execute in a single pass
through the table. The Dave (subquery) version will probably need to make 3 passes through the table get the same results.
- er, it's Ian, not Jan :-)
As was mentioned in another response, the
Jay Blanchard wrote:
[snip]
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3
[snip]
What is the purpose of DISTINCT here?
[/snip]
It's a hold over from other RDBMSs that I have worked with in the past.
The syntax is still valid and I pretty much automatically type it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
Ian Sales wrote:
- I always concatenate the wanted criteria (status, in this case) with a
unique ID field in order to be absolutely certain I'm counting every
instance of each value of status. I've not investigated to see whether I
*really* need to do this, but it feels safer...
- ian
Then
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from
.
The group by version returns value and count columns, one row for each value
counted.)
I'd conclude that the group by version is faster, and also that you ought to
test your own data (:-).
Here are the two queries I used:
SELECT
(SELECT count(*) FROM patients WHERE last_name
||
+--+--+--+-+-++
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from account
||
+--+--+--+-+-++
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count
Jerry,
You could also have 3 rows returned by running the following:
select count(*)
from account
where status in ('received','send','canceled')
group by status
;
This will return 3 rows, similar to the following
+-+---+
| status | count
I have table account (see below). I need to get count of received,
count of send and cound of cancelled records. I know that I can do
this in 3 queries.
#1. select count(*) from account where status='received';
#2. select count(*) from account where status='send';
#3. select count(*) from
1 - 100 of 153 matches
Mail list logo
