MYSQL :: Slow Count(*) On Many Indexed Rows

MySQL Timing Out? Really Slow Queries, Already Indexed.
For whatever reason, MySQL does not seem to be executing. So, I ran a few stat functions and here is what I got:

Uptime: 220372
Threads: 39
Questions: 18748899
Slow queries: 808
Opens: 28723
Flush tables: 1
Open tables: 4096
Queries per second avg: 85.078

The "open tables" worries me for a few reasons. First, because it is 4kb (exactly, since it is divisible by 1024, evenly). This makes me think that maybe I am hitting a ceiling.

Second, there are a few variables in MySQL that equal this, and maybe it's a cap on something. Here are the variables that equal 4096:
query_cache_min_res_unit = 4096
table_cache = 4096
transaction_prealloc_size = 4096

Do any of those have anything to do with this? Am I even close? Are any of those values from mysql_stat a big deal?

Slow Select Count(*)
I have a table with 75,000 rows.

An Explain tablename shows this

'TourPartsID', 'int(10) unsigned', 'NO', 'PRI', '', 'auto_increment'
'TourPartID', 'int(10) unsigned', 'NO', 'MUL', '', ''
'TourPartTypeID', 'int(10) unsigned', 'NO', 'MUL', '0', ''
'Language', 'int(10) unsigned', 'NO', '', '0', ''
'XML', 'mediumblob', 'NO', '', '', ''

When I do a Select count(*) from table, it takes about 12secs to return my number. This is occuring in the QueryBrowser and from the .NET library when it calls the Stored Procedure that does the Select count(*)....

My only quess that is my last column is a MEDBLOB but since I am NOT indexing that, I would figure MySQL ignores it.

Slow Select Using Count(distinct) In A Table Bigger Than 100000 Records
Recently I started using MYSQL in my enterprise. I made a table which has around 100000 records. The problems is that it is really slow.. Im trying to do a query in which I get the number of distinct users per day.

This is my query:

select date(startedDate) as mydate, count(distinct(Users)) as users from Mytable
group by mydate

It is really simple and it does it correctly but it takes one minute.. One minute is not too much time but i need to insert around 10 000 000 records and thats what worries me.....

Query With 3.3million Rows Is Slow?
I'm not that great with MySQL...so I was hoping someone could help me out.
The query I'm running is too slow...can anyone tell me what I can do to
speed it up..if I can at all? I was wondering if because ZipListMatrix has
3.3 million rows that 8 seconds is all the faster it's going to be. Any
help is greatly appreciated! I have already "optimized" the tables.

Why Does The Slow Query Log Show More Rows Than Exist?
# Time: 070528 17:14:57
# User@Host: counter[counter] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 7 Rows_examined: 120647
SELECT SQL_CACHE `webpageUrl`, `webpageName`, COUNT(*) AS `count`, (COUNT(*) / (SELECT COUNT(*) FROM _1_log)) AS `pct` FROM _1_log GROUP BY `webpageUrl` ORDER BY `count` DESC LIMIT 7;

mysql> select count(*) from _1_log;
+----------+
| count(*) |
+----------+
| 111824 |
+----------+
1 row in set (0.00 sec)

Indexed Totals
don't know if that subject is correctly put, but here's what i'm trying to accomplish:
I want to be able to tell how many rows in a given table, and for a given INDEXED
column, carry any given ID.
Example: suppose I have a field named 'IDNumeric' defined as decimal(5,0). Now suppose
one ID is: '56007'. I want to be able to tell how many rows in the entire table have
that ID. I know that I can use the select keyword, but i'm wondering if there's another
way, because the table i'll be doing this for can be up to 350 Million rows, and the
vast majority (probably close to 99.9%) of the rows will have mutually exclusive IDs.
I am only concerned with that small percentage of rows that have duplicate IDs

Indexed Search Capability
I'd like to have indexed search capability on column A, column B, or
columns (A,B) for a given table. According to the MySQL manual, a
multi-column index of (A,B) will provide "leftmost prefix" indexing, so
that I can search on column A uniquely or columns (A,B) together, but
not on column B uniquely. Therefore, I would need a second index on
column B.
My question: would there be any benefit, whatsoever, to having a
separate index just for column A, or is that completely redundant to
the multi-column index (A,B)?

Get A Count And Some Rows
Can I get a count on the total rows but not return all of them, let's say just get the 10 first rows.

Count Rows
with php and mysql:
How we can count rows in a table?

Count The Rows
I've built quite a few large join statements, which cross-reference 6 or 7 tables each. I'm using LIMIT to allow for pagination, which works fine.
However, I need to find out how many rows were returned in total, and don't fancy throwing in another join to count() the results with some where statements.
Anyway I can quickly find out what would've been returned without the LIMIT?

Invalid Distinct Recordset Returned On An Indexed Column
I have a table with about 1.2 million records. I have an index set on a column.

For close to two years, this query has worked perfectly fine:

SELECT DISTINCT `Mgmt_Area` as thevalue, `Mgmt_Area` as valueid from qcdata ORDER BY thevalue

Note, this is a programmatically generated query based on some user selections. This query actually is used to create a listbox. The index has 62 separate values.

This query would usually return: Code:

Count Multiple Rows
I have a table that tracks dealer transactions. The fields are Date, Dealership, Amount, A, D, W, F. A=Approved D=Denied, W=Withdrawn and F=Funded. I have made it where if a loan has been approved then A would =1 and D W F would be null. Same goes if the record was withdrawn W would =1 and the other would be null.

So here is my problem:

I want to group by dealership and then count how many were approved, denied, withdrawn, and funded. i am running version 3.23.58. After doing much research I either need to do unions or subqueries. However, both are not availble until 4.x. Is it possible to do what I am looking to do without upgrading?

Count() Rows With 0 Matches
I am using the code below to select all the 'centres' in the database that have 'sessions' today.

SELECT c.*, r.region, COUNT(*) AS count
FROM centres c, regions r, sessions st
WHERE c.c_id=st.c_id
AND st.session_date=CURDATE()
AND c.region_id=r.region_id
This works and the results might look like:

centre / count
centre1 / 20
centre2 / 10
centre3 / 3

However, is there a way to get MySQL to also select centres from the database that have no matches in the sessions table? So the results will look like this:

centre / count
centre1 / 20
centre2 / 10
centre3 / 3
centre4 / 0
centre5 / 0
centre6 / 0

Count Same Field In Rows
i need to calculate the number of the same IP address that appear in the database, how can i do that?
example of the database:
IP Address Date and Time
127.0.0.1 5/23/2007
127.0.0.1 5/24/2007
192.168.1.2 5/25/2007
192.168.1.5 5/26/2007
127.0.0.1 5/27/2007

Count Identical Rows
I've got a log table looking like this:Code:

user | action | time
| |
a | click | 2005-05-05
a | login | 2005-04-05
b | logout | 2005-03-02
b | click | 2005-01-25

And now I wanna display the statistics in a neat interface. But I'm having problems... is there a way to select from the above table and get rows like this:Code:
user | click | login | logout
| | |
a | 56 | 23 | 45
b | 45 | 2 | 0
c | 12 | 32 | 32

Perhaps this is more than I could expect from MySQL?

How To Get Count Of Rows Returned While Using Group & Having?
i'm using the having keyword to find certain set of rows and they are working properly.

but what i want is , i want to count the total number of rows returned by this query using mysql.

select name,count(date) from emp group by date having count(date)>3;
this returned returns all the names that have more that 3 entries in the same date. it returned 5 rows and how can i get the count of rows(5) the query returned .

Retrieve Everything AND Count Rows In One Query
set rsminmax = con.execute("SELECT * FROM `minirules_minmax` where RuleId = '" & contractId & "'")
set rsminmax2 = con.execute("SELECT COUNT(*) FROM `minirules_minmax` where RuleId = '" & contractId & "'")

Is there any way to do this in one SQL query?

Count Fields Over Multiple Rows
I have a database like this

id, field1,field2,field3,field4,field5

Database contains 100 rows, some rows have no fields filled, some
1field , some 2 fields etc.

How would i count the number of fields filled in total?

So the outcome is (number of fields filled in row1)+(number of fields
filled in row2)+(number of fields filled in
row3)....................+(number of fields filled in row100)

Count Number Of Rows Returned?
this is my connect and everything code:

<?php // connect to the mysql database server.
mysql_connect ($dbhost, $dbusername, $dbuserpass);
mysql_select_db($dbname) or die(mysql_error());
$query = "SELECT DISTINCT category FROM $tuttable";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result))
{
echo ($row['category']." | ");
}
echo count($row);
?>

Notice "echo count($row);" How do i get this to work properly. It only returns one, because there is only 1 that it is working on. I want to return the count of all the rows that fit that query.

Select Count(*) Showing -1 Rows.
In one of my preliminary tests with mysql, the below statement is showing the following way. What does -1 rows signify in the table with the select count(*) query. Is this (-1 rows in the query result) an error condition to be taken care ?.

bash-2.03# ./mysql -u test
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 8 to server version: 5.0.18-max

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Code:

Count How Many Matching Rows Exsist
I am new to SQL Querries, can someone help me figure out how to get a result?

I have a column in my db called; emails

some of the email addresses in this column may contain similar domains:

person1@site.com
person2@site.com
person3@someothersite.net

I need the result to count and end up being 2 not 3

Not sure how to make the sql statement:
COUNT (*) FROM database_tabe WHERE email='$email' LIKE '%@%.%'

Not sure how to get this result...

Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code:

$conn = mysql_connect("localhost:3306", "root", "********")
                                    or die ("Error With Connection");
        echo("connected<br><br>");
        $db_sel = mysql_select_db("game",$conn)
                                    or die ("Error With Database");
        $check = "select * from users where 'username' = '$username'";
        $db_sel = mysql_query($check,$conn)
                or die (mysql_error());

Count Or Select Only The Populated Rows In A Column?
I'm trying to COUNT the number of populated rows within a column named, 'column_03', but first instead of using COUNT i'm using SELECT to see where the numbers are coming from.
lets say i use the following code...

SELECT column_03
FROM table_name
WHERE table_name.column_01 = 'IN''
AND table.name.column_02 ='GENER"

the result is a total number of 10 records. i can see that 'column_03' has 6 rows that are populated and 4 rows are empty. now what i want to do is select only the 6 rows that are popluated, once i now how to do that i can then use COUNT. Any suggestions?

Limiting Rows Returned Based On Count
say if I have 3000 records in my db, and I want to get rows 200~300 when orderd by a specific field in this table, is there a easy way to do it?

I have something like:

select * from table_name order by ID desc limit ....

but I am not sure if this syntax allows me to do what I am trying to do .

Return A Count Of Number Of Rows Before Desired Row.
Anyone know how? find_in_set doesn't seem to be able to do what I want since I am not searching through an array but am actually running a query.

What I really want is to be able to select all until something = something. I've searched the realm of google but cannot come up with anything useful, and so I am wondering if maybe I don't know the right words to be looking for.

Problem With Left Join And Count, Returning More Rows Than What It Should
Am having a problem with a query, strangely ...

PHP

SELECT *
FROM table1 AS mt
LEFT JOIN table2 AS pt ON mt.p_id = pt.p_id
WHERE my_field = 'somevalue'

Is returning a much bigger number (12 rows) for me, then what it should.

PHP

SELECT *
FROM table2
WHERE my_field = 'somevalue'

Is returning only 2 rows

Count One Table's Rows From Multi Table Query
here are my tables (condensed)

FEEDS
feed_id
site_id

SITE
site_id
site_name

ARTICLES
article_id
feed_id
link

I want to create a query that returns the total number of articles for every site_id (which is unique in the SITE table). I have this:

PHP

$gsite = mysql_query("SELECT site.site_id, feeds.feed_id, COUNT(articles.article_id) AS acont FROM site,feeds,articles
WHERE feeds.site_id = site.site_id AND articles.feed_id = feeds.feed_id group by site.site_id", $connection)
or die(mysql_error());

The query does not use JOIN, ON and all that good stuff.

I just need the following variables to run through a loop:

site_id
the number of articles rows per site_id

Agregate Count Return All Rows, Left Join
I have 2 tables on a lyric discussion site

Titles
   title_id
   title_name
   title_lyricist
   title_lyrics
   title_artist
   title_entered

And POSTS
   post_id
   title_id
   post_author
   post_text

I want to be able to list all the titles, and count the # of posts for each title. The problem is some titles have zero posts so my query ignores thos titles, butI syill want them returnd where Ill add a ')' for count.

This is the best i could come up with, but still only those that have posts are returned. I still want the titles that don't have a match in the posts table to be returned with a total_count of zero.

Trying To Pull Id, Count And Title But Lose Rows When I Add Extra Join
i'm trying to extract some information from my database, the query being

PHP

SELECT grps_c.catid, grps_c.title, COUNT(grps.groupid) AS COUNT
            FROM grps
            RIGHT JOIN grps_category grps_c ON (grps_c.catid = grps.catid)
            GROUP BY grps_c.catid
            ORDER BY grps_c.title

which works fine. however some of the groups (grps.groupid) are hidden and i don't want to count them, so my thinking was add

PHP

LEFT JOIN grps_setting grps_s ON (grps_s.groupid = grps.groupid AND grps_s.hidden_group != Ƈ')

however adding that removes the rows that have a 'count' or NULL or Zero.

#1136 - Column Count Doesn't Match Value Count At Row 1
I'm getting the above error with the following SQL Statement. I cannot seem to find the error in the code. The select statement does pull multiple rows.

I'm using my SQL version: 4.1.19

Any clue on why this isn't working?

INSERT INTO `Grants` ( `Project_Code` , `Grant_Code` , `Fiscal_Year` , `Capital` )
VALUES (
(
SELECT Project_Code, Grant_Code, FY, SUM( Capital )
FROM Grants_Temp
WHERE Project_Code = 'OSUT'
AND FY = '2006'
AND Claim_Month = '072006'
GROUP BY Grant_Code
ORDER BY Grant_Code
)
)

Column Count Doesn't Match Value Count At Row 1
I am getting the following error when I run my query.
Column count doesn't match value count at row 1

I have looked up this error and have checked and I appear to have the right number and names in my query. In the DB table I have 34 columns, and that is what I have in the query.

The last 4 in the query are for the names of the images being uploaded, but am not sure how this all works, so I don't know if I need them, but have them there until I know for sure. Comment, Purchase and Remarks are not used in this form, but I have added them so everything is being shown in the query. Code:

Count(*) As Count And TotalCount Of Count
data in myTable1

(n) country_id
(1) 3
(2) 1
(41) 1
(5) 2
(6) 3
(7) 4

data in myTable4

(time) param
(10 : 10) c=4
(10 : 12) c=2
(10 : 30) n=41&k=5
(10 : 35) c=1
(10 : 37) n=5
(10 : 50) c=2
(10 : 54) c=2
(10 : 55) n=1&cate=6
(11 : 12) c=2
(11 : 15) n=7
(11 : 20) c=1
I have data in myTables like the above.

I have the following code.

code

(select left(time,2) as hour, count(*) as count
from myTable4
where

left(param,1)='c'
and
substring(param,3,1)=2

group by left(time,2)
)
UNION all
(select left(time,2) as hour, count(*) as count
from myTable4,myTable1

where
left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=2
group by left(time,2)
)

order by hour
And the code above produces the following result.

result

(day) count
(10) 3
(10) 1
(11) 1
The following would-be code doesn't work correctly, but it will show what I want.

would-be code

(select left(time,2) as hour, count(*) as count,
sum(count) as totalCount
from myTable4
where

left(param,1)='c'
and
substring(param,3,1)=2

group by left(time,2)
)
UNION all
(select left(time,2) as hour, count(*) as count,
sum(count) as totalCount
from myTable4,myTable1

where
left(param,1)='n'
and
substring(substring_index(param, '&', 1) ,3)=myTable1.n
and
myTable1.country_id=2
group by left(time,2)
)

order by hour
And the following is my target result.

target result

(day) count totalCount
(10) 3 5
(10) 1 5
(11) 1 5

Slow
What generally would be the reason why all my db driven sites are running slowly or even hanging. I am on braodband speed but just changed hosts.

Searching For Rows That Depend On Other Rows In Some Fashion...
Maybe this is a very stupid question. I'll try anayway. I'm using
MySQL 3.23.52 on RedHat 8.0.

I have a very big table (several millions of rows). Each entry
actually constitute a word in a big text. Among other fields we have
an ID for each word which represents the postition in the text. Now I
want to search for short phrases.

For example "welcome to sweden". This means i want to find all
occurences of the word "welcome" with ID x, where we have the word
"to" with ID x+1 and the word "sweden" with ID x+2. I've tried doing
this recursively with temporary tables - but a find myself hitting a
wall as i'm not allowed to refer to 2 different temporary tables in
the same query...

Resorting to another type of data strucutre, full text index etc.. is
unfortunately not an option. We only need this as an add on feature if
we can do it. We believe that indexes, data structures etc.. are near
optimal as is.

Please use my email for further conversion since I'm not a frequent
usenet reader.

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db.
Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4.
What I would like to do is take the entire row and duplicate it by the number of the quantity field.
This would result in the same product 4 times instead of one product with qty of 4.
Reason, I am going to serialise all the products so that they each have unique barcode from the id field.
I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??

Difference Between Count(*) And Count(1)
What is the difference between count(*) and count(1)?

Difference Of Count(*) From Count(1)
Is it true that count(1) is more efficient than count(*)? They say that count(*) still goes through all the records without needing to.

Slow Query
i have this query on a website/webapp that has expanded beyond all expectation. It now takes nearly 30secs to return results from the database

SELECT cl_t.Client_ID, Buyer_1_Title, Buyer_1_Prename,
Buyer_1_Surname, Tel_No, Mob_No, Buyer_2_Title,
Buyer_2_Prename, Buyer_2_Surname, Email_Add,
Price_Max, MAX(activity_t.Date) AS lastcomm
FROM cl_t
INNER JOIN cl_want_t
ON cl_t.Client_ID = cl_want_t.Client_ID
AND Agency_Code ='$agencyloggedincode'
AND Deleted = 'N'
LEFT JOIN activity_t
ON Buy_Sell = 'B'
AND Ref_No = cl_t.Client_ID
WHERE cl_t.Sales_Agent_ID = $agentid
GROUP BY cl_t.Client_ID
ORDER BY $order
The problem is the call to MAX(activity_t.Date) AS lastcomm

activity_t holds all known contact with all known clients and as such is a very large table, the call to search through all of these records and return only the date of the last entry for this client is taking the time. If I remove this from the query I get results in 3 seconds.

I have indexing on activity_t.Date & activity_t.Ref_No

Question, is there a way of doing this quicker within the table I already have, or should I create another table that just holds the last update date for each client, and get the date from this much smaller table.

Slow Authentication
MySQL V 5.0.18 on SUSE 10.1

I'm not a complete *nix noob, but I sure as hell ain't a *nix or MySQL pro.

This is a new installation. Everything screaming fast. Unless it deals w/authentication.

Try to get in w/SQLyog from W2K locally ... intitial connection takes ~20 seconds. Then everything screaming fast.

Web Server (W03) attempts to connect via MyODBC ... same result ... initial connection takes ~20 seconds. Subsequent queries screaming fast.

VNC into the box at any time ... everything fast. (would seem to eliminate network/connection issues)

Why Is This Query Too Slow?
I find this query to be exceptionally slow(around 2.5 seconds), could some tell me why this is so?

MySQL
SELECT st.profile_views,count( DISTINCT p.ID ) news_submitted, count( DISTINCT pv.ID ) news_voted, count( DISTINCT pcom.ID ) news_commented, u.joined, u.weight FROM users u LEFT JOIN posts p ON p.submitted_user_id = u.user_id LEFT JOIN post_votes pv ON pv.user_id = u.user_id LEFT JOIN post_comments pcom ON pcom.user_id = u.user_id LEFT JOIN stats st ON st.user_id=u.user_id WHERE u.user_id='john' GROUP BY u.user_id
I traced the cause to this line
count( DISTINCT p.ID ) news_submitted (from LEFT JOIN posts p ON p.submitted_user_id=u.user_id)
But when i execute something like this

MySQL
SELECT count( DISTINCT p.ID ) news_submitted FROM posts WHERE submitted_user_id='john'
it is quite fast (around 0.03 seconds)
So why does it slow down when i'm joining the above query with 3 other tables ?
Should i use INTEGER for user_id instead of string like 'john'?

Slow Query Log
my slow log is catching a slow query, however the timestamp for the query is "0". I also placed a timestamp on the query to echo out to the results page, and it is about 4 thousands of a second. Why is it showing in the slow log?

Slow Subselect
I've got two tables:
lo_users: nickname|id|...
lo_friends: from|to|...

The following query takes < 0.01 sec:
SELECT IF(`from` = '10855', `to`, `from`) userid FROM lo_friends WHERE (`from` = '10855' OR `to` = '10855') AND STATUS = '1'

...but if I use it in a subselect, the whole thing takes about 0.54 sec:
SELECT u.nickname FROM (SELECT IF(`from` = '10855', `to`, `from`) userid FROM lo_friends WHERE (`from` = '10855' OR `to` = '10855') AND STATUS = '1') f LEFT JOIN lo_users u ON u.id = f.userid

What can I do to make the query faster? "from" and "to" are indexed and lo_users.id is the primary key.

Slow Query Using NOT IN
I am migrating a MSSQL server to MySQL. I know the following SQL is valid for both servers, but MSSQL finishes execution of the query almost instantly, and MySQL has been running the query for the past ten minutes and still is not finished. There is basically the same amount of data in each database. Does anyone know ....

Update Too Slow
I need to update 25 * 5000 records, if I do one at the time it takes too
long time, do any one have a good proposal ?

MySQL Slow
I had downloaded a few years back mySQL v3.51 installed but never used it. Now I wanted to convert some B-TREE databases to mySQL and did some testing via ODBC to insert 70,000 records: My results:

MS ACCESS: ~60,000 msecs
MYSQL v3.51 ~18,000 msecs

Impressed with the speed, I went ahead and got the latest MySQL v5.1. Uninstalled the older version, I had nothing there to preserve, so I did a simple new install with MySQL v5.1. I noticed the size of the files and BINEXE increasted by 1,000,000%. Ok, Bulky. Not a problem.

I reran the same ODBC test, and now I got:

MYSQL v5.1: ~450,000 msecs or 7.5 freaking MINUTES!

What the hell happen? Nothing was done. I'm knew to MYSQL. I just installed it with all the defaults. I did choose "developer's machine" for the "optimizer wizard"

I can't redistribute MYSQL v3.51 and force it down people's throats! I have to use what they are using already, if already installed. Not even my current system takes 1 minute to add 70,000 records. Why 7.5 minutes? All it is simple inserts/free statements.

Join Too Slow
I'm creating a query that use Join clause. I tested it in MySQL 4.0.24 and with MS-ACCESS. . . . in MySQL is slow!!! any suggestion ?

Slow Connection
I build an application and installed it on many machines. In every machine except two, the program works without problems. On this two, the connection with database is too slow.

I saw the opened doors with 'netstat' and the computer opens about 5 or 6 ports (to the port 3306 of the mysql server) before sucessfuly connect with MySql Database and execute the sql. I don't know what could be happening. I realy need to fix this because the progrm is too slow with this error. Could anyone know what could be happening??