MYSQL :: MySQL Timing Out? Really Slow Queries, Already Indexed.

Slow Count(*) On Many Indexed Rows
we have a 10 million rows table. One field 'stamped' is either 'yes' or 'no'.
About half the rows are 'yes' and half are 'no'.
Table is indexed on 'stamped'.
We just need the count on 'yes'.

SELECT count(*) from T where stamped='yes'

or <>'no' or >'n'... is very slow.

Is there a better approach / query to get a fast result?

Slow Queries
I had a working web page that queried 20 tables and returned the data in the form of a table ... all of a sudden this stopped returning the results , and on investigation, up to ten tables, the query time is about 0.01 second ... but as the number of tables is increased to fifteen, the query time increases to 60 second .. and then gets too slow with nothing being returned at all.

A few days ago, this was all working fine. There have been no changes to the code on the web page ....
The version of mysql is 4.0.20, the server is a dual 1 MHz Xeon with 512 meg of RAM, running linux.

The tables have up to 15 fields each, and there is only simple text or numerical values in the fields.

Can anyone suggest what might have changed to suddenly slow down the query ?

What Causes Slow Queries
What causes periodic slow queries? I have checked my slow query logs and for some reason everyonce in awhile, a query thats never slow might be for example, one took 3 seconds to execute and every once in awhile a chat might take 10 seconds of cpu time while rest of the time 0.09...why is it it flexuates so much?

Slow Queries
I just got a new dual opteron system, with raid 01, 2gb ram, and fedora
core linux running 2.4.22-smp kernel. For some reason mysql is running
pathetically slow.

Queries that should take 2ms occasionally take up to
20 seconds. It isn't every time, but almost once per page. The problem
usually occurs with queries accessing the large tables (up to 1gb), but
not always. I've tried 2 versions I compiled myself (with flags
suggested in the readme), as well as the version off the mysql website.
All were 4.0.18, and all had the problem.

Slow Queries
I have set long query time to 2 seconds in my cnf file. Therefore all the queries taking more than 2 seconds are logged in the file called mysql-slow.log and the file size is 20 Mb.
Is there anyway to sort this file and find out the queries those are taking the most time so that I can optimize only those ones

Slow Queries!
I have a website which has a users table in a mySQL database. This users table is large (It has about 25 columns - most varchar(100)) but only has about 10000 records. The records contain user information which is searched with a javascript form.
My problem is that when I click to 'view all', it takes about 7 seconds to load. This seems a lot?
Does 25 cloums seem sxcessive in a table? Can anyone point me to some good tutorials / docs on improving query performance? I have defined the colums as best as I can, but I am using SELECT * from table, would selecting individual columns make a big difference?

Enabling -- Log - Slow - Queries
I want to enable --log-slow-queries of MySQL.

How do I do it without restarting the DB server?

Slow View Queries
I have a fairly complex view which, when you execute a SELECT * shows all info very quickly (0.24 seconds to return nearly 3000 rows). However, when I start throwing slightly more complex queries at it, things really slow down - an average query can take 40 seconds to execute.

Slow Queries And Optimization
I am hosting a site with an increasing number of members. Letely the site has become slower because of too many queries are being used.
Is there a way of asking the server which queries are used most often, as well as asking which queries take a long time to process so that I would know where to start my optimization process?

Optimizing Slow Queries On Millions Of Records
I have a table that has millions of records in it.
About 100 records are added every 5 minutes (one per OIDID) (the sample
provided below has data for 2 OIDIDs (99 and 100)
And I have a webpage that executes 9 queries one after the other, and then
displays the results on the webpage.

When the database was empty, this process was very quick.
But, as the DB grew, it became slower.
Now it takes about 38 seconds for all queries in the example below.
MySQL 4.1 on Windows

I have tried different indexes, and they do help and are used, but the
queries still seem to take a long time. Code:

Slow Queries, 4 Million Records, Need Educated Advice!
I have created an app a few years back to store some records in a DB.
According our calculations we were never to exceed 500,000 records in
the DB. Seems we were off by a decimal point or so.

I set up a FreeBSD box with MySQL three years back and it has been
filling up. One table has over 4,000,000 records. Yes, four million.

As a web developer and not a DBA, I have struggled to upkeep the server
the best I can. As of the last one million records the server has been
struggling to keep up with multiple requests and as you can imagine the
user base is growing too.

Hardware:
Dual Xeon 3.06 Ghz
4 GB ECC RAM
800GB RAID5 SATA array

Software:
FreeBSD 5.3
Apache 2.0
PHP5
MySQL 5.0.2

Basically I have to perform a search on one of two columns in this huge
table (10 columns, 4 million rows).

The table is MyISAM with a single primary key that is used largely for
updating row data.

Most records are ten digit numbers for one column and a ten digit
varchar for the other, but sometimes either column can be a series of
characters up to 100 chars long, so each column is set for
varchar(100).

The action performed is

SELECT count(*) FROM tableName WHERE col LIKE "%123%"

then...

SELECT * FROM tableName WHERE col LIKE "%123%" LIMIT 0,25

with "%123%" being any random string typed into a search window.

MySQL Timing Out
When I try to pull out all my data out, the DB appears to be timing out before I get all the data.

There is not a where clause in the select.

Is there a way I can speed up the query? ........

MySQL Timing Out
When I look at MySQL in the Windows task manager I see that it isn't using much CPU power, nor is anything else. However, my hard drive light is going non-stop when I try to load a page and whenever the page times out it is always on a line of code fetching a query result from the database.

What could MySQL have suddenly started doing that even simple queries require vast amounts of hard drive usage?

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)?

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:

Big DELETE Timing Out
I'm trying to run the following query to delete categories and sub-categories that have 0 products in them but the query keeps timing out. I have about 20,000 products in about 700 categories. This is just test data but I want to make sure I can process such a large amount of data.

MySQL
DELETE c1.*
FROM categories AS c1
LEFT OUTER JOIN categories AS c2
ON c1.id = c2.parent
LEFT OUTER JOIN products AS p
ON c1.id = p.category
WHERE c2.parent IS NULL
AND p.category IS NULL

I'd like to use LIMIT but you can't use it when you have 2 or more tables.

Is there a way to make this go a lot faster or break the query down into parts so it runs a couple of times?

Timing Out Problem?
I've got one database server that works fine when transferring large amounts
of data, but a new one that has been setup seems to want to timeout or drop
the connection after a period of time. I've tried to compare the variables
and they seem to be the same. The versions of MySQL are slightly different
with the good one being 3.23.41 and the one timing out being 3.23.58

Any ideas on what I can check for that would be different or help to keep
this new server from timing out? Seems to want to transfer 3-4000 records
then die. Where the other server will transfer all 60,000+ records.

Connection Timing Out
I have a solaris 10 server and mysql 5.0.37 compiled 64 bit. I can update the mysql database, create databases, etc, but when I tried to create a simple two-field table, and once again, was hit by the same infamous Error 2013 (Lost connection to Mysql Server). I noticed this when I tried to optimize a database I had imported, but now I'm finding it a bigger problem.

Select Count(*) Timing Out
I'm doing a "select count(*) from table1" and it takes forever, more then 3 minutes, for the result to come back. I'm running MySQL 5.0.19 on RedHat, I know my table has 51M records. When I run a "show table status where name='table1';" it returns the results in a few ms and gives me a record count. My table is defined below, I've tried forcing use of each index individually and forcing no indexes and all combinations and always the same result. Server is an intel based 64bit cpu with 16GB ram with pretty much nothing else going on......

Timing A Trigger Or A Procedure To Execute
I need your help regarding how to time a sql procedure or a trigger to get executed ie., I would like to call the procedure or trigger automatically on a pariticular date and time after satisfying the required conditions.

Sql Timing With Transactions - Delete Vs Select
I have a sql command I want to run and time but rollback the results afterwards.
I tried the code below but I don't get any timing output. If I do a select instead of delete I get output.

Any ideas what's going on?

start transaction;
DELETE FROM testtable WHERE col1 = 1;
rollback;
start transaction;
DELETE FROM testtable WHERE col1 = 2;
DELETE FROM testtable WHERE col1 = 3;
rollback;

I don't get any output from the delete but here's the select output:
count(*)
0
count(*)
0
count(*)
19841

Looping Through Insert Statements Is Timing Out, Alternative?
I am looping through and creating about 600 inserts/executes in PHP and it's timing out.

I have tried aggregating them into one string that ends up like:
INSERT INTO table VALUES(val1, val2,...);INSERT INTO table VALUES(val3, val4,...);INSERT INTO table VALUES(val5, val6,...);...

But is errors out.

I am using PHP5 and MySQL 4.1. How can I get around that? Is there an alternative?

If prepared statement is a way around it, any easy examples to look at?

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.

MySQL Very Slow.
I have this one site that slows down all my others because the queries are so massive.

For example one of these queries I use to-do a search by a user's account number. I also get the position he is at on the list, and in order to-do that I need to select ALL the rows.

For example, I filter out the other queries in php.
CODE$num = 1;
$q = mysql_query('SELECT * FROM lists');
while($r = mysql_fetch_assoc($q))
{

   if($r['account']=='accountNum')
   {
         print 'You are at pos. num '.$num.'<br>';
   }
   $num++;
}

Why Does MySQL So Slow
I just changed to use MySQL few days ago but it was a bad idea. My server now is running very slowly with the database. I'm using Perl5 and DBD::Mysql in my script. The system is Linux9, Apache2.

I looked at these mysql pid and saw a lot of activities (about 400) while there are more 100 users online at this moment and lots of running under a the same pid number.

MySQL Slow Log
I have the long query time set to 15 yet MySQL is still showing results with a query time of 0 in the slow query log.

It says enter time in "seconds" in the MySQL Administrator but did it mean in milliseconds??

Slow MySQL
mySQL has been running very slowly and I am getting errors. First I did 2 things I raised the ServerLimit number (apache) to allow for more connections, I also raised the max conncetions in my.cnf. I do not know if this took effect? That should have worked. But basically in phpmyadmin i get this error frequently. I am getting more traffic so I think it is that.

MySQL said: Documentation
#2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)

Complex Queries Versus Multiple Simple Queries
I am constructing a database to contain information about stories posted on my site. Information included will be things like title, author(s), genre(s), story codes, synopsis, etc.

I worked out that storing this information properly, so that it can all be searched on, could take as many as ten tables.

My question is this: Is a single complex query really better (more efficient for the server) than multiple simple queries? In other words, I may need the information for as many as 25 or even 50 stories for a single page.

Is it better to get all of the information out of a single, massive, complex query, or is it acceptable to get the information essentially one story at a time, which could mean 25 or 50 simple queries...?

Slow Response From MySQL
I was wondering if any one can help me out, I am literally tearing my
hair out with an availability search I have written.

Previously I was selecting all the records from two databases but
noticed the response time was very slow anything up to 20 secs.

I tried to streamline the search by only selecting the columns I
needed from the tables and created indexes for each of the tables for
the required rows but now the results are executing in around 50 - 60
secs, which is ultimately alot slower.

The SQL query I am using at the moment is this:

SELECT villas.id, villas.resort, villas.beds, villas.owner,
villas.air_con, villas.walk_beach, villas.walk_shop, pricing.id,
pricing.week, pricing.price, pricing.availability FROM villas LEFT
OUTER JOIN pricing ON villas.id = pricing.id WHERE pricing.week =
1073088000 AND pricing.availability = 1 AND villas.beds > 0 AND
pricing.price > 0 AND ( villas.resort = 'cala_dor' OR villas.resort =
'pollenca' ) GROUP BY villas.id ORDER BY villas.owner DESC ,
villas.beds ASC

MySQL Slow In Windows NT4
I am running the Apache web server, MySQL v4, and PHP on an NT4 server.
Apache runs great, but the auction software I am using (Web2035 Auction
software written in PHP) is very, very slow.

Sometimes it takes 20-30 seconds to bring up an auction page from the items
table which has less than 200 records in it.

Can anyone can give me some pointers on where to start looking? (I don't
know if the bottleneck is with MySQL or PHP or what I might need to look at
to enhance the performance of either package.)

MySQL Is Slow, Trying To Optimize
It just seems that my system is slow, adding records, etc. I tried to optimize it, saw that there was a my-medium.ini file, read that is was for medium sized system. I replaced it with my.ini (yes I did make a backup, thankfully) and restarted mysql.

Pretty much it hangs. I tried to connect with QueryBrower, did a Select Count(*) and just froze. I admit that I didn't wait for ever, yes I know I Select Count(*) takes a long time but I gave up after waiting 3x as long as I normal did. Also the logs screen froze in administrator.

MySQL Slow Starting Up
I am using a Windows 2003 Web Edition Server running IIS and MySQL 4.1. I had to restart the server the other day and it took MySQL almost 2 hours to come back up. I was watching it on my task manager and it went up to approximately 60,000k in 10k increments before I was able to access MySQL.

My CPU usage was hovering around 3% all this time. Everything else came up on the server just fine. This happens every time I have to restart my server. The reason I have to restart my server most of the time, is because MySQL locks up on me.

Is there any settings I can change that would help me here? Any help would be greatly appreciated.

One more thing to add. I am using the default my.ini file. We have almost 100 different databases on the server and have around 20000 tables within all of these databases combined. All of these tables are InnoDB tables.

MySQL Running Very Slow
I have the following INSERT SQL, which runs very slow (For 2 full days it had not done anything) on a 2 processor Compaq machine running Suse Linux. Code:

Slow Running MySQL?
I'm hoping someone may have an idea what could be causing the slow loading pages of my web-site. I'm not positive, but I'm relatively sure that it's related to a problem with MySQL database.

I purchased a program called Sam3 for broadcasting our radio signal over the internet. The software requires the use of MySql in order to set up web pages for our web site.

The problem is, that while everything works GREAT (requests can be made from the site, songs can be searched, the history of what we've played shows up) the pages load PAINFULLY slow. Other users of this software have had no such problems so I'm at a loss.

I know from testing that the PHP script for the web pages is fine, I have plenty of bandwith both upload and download available on my DSL line, and while the computer running MySQL is behind a router I have tried plugging the DSL line DIRECTLY into the host computer and it makes no difference.

For all appearances it seems the problem is that it takes MySQL forever to gather and provide the information needed for these pages. Does anyone have any idea what I should be looking at to solve this problem?

I do not know the MySQL program at all, so I get somewhat lost when people start throwing out technical terms, but this has been really frustrating because no one else seems to have the problem and everyone so far has just told me to check the things I've already checked.

MySQL Connection Sometimes Slow
I have been developing an application in Visual Basic that accesses a MySQL database either on the local machine, or a remote machine. Locally, database connections run smoothly. However, when I try to connect to the remote system (another computer on my local network) some databases take longer to connect than others (sometimes 10 - 20 seconds).

The application usses the myvbql.dll (sorry lost the link, found it through google) wrapper around the libmysql.dll file to perform database actions. The application seems to hang when it tries to actually make the connection to the datatbase. This is only happening on the remote database server, and does not happen on every connection to that server.

Slow Mysql Select *,
I have a sql table with more then 20,000 rows in a table called summary and when I run "select * from summary" it returns a exucutions time of 0.5 seconds in php which is kinda slow if you ask me, now I know that the * type for select isnt the fastest way to fetch data but its the most convenient for me.

Many Smaller Queries Vs. Large Comlicated Queries
I am wondering if any one can help me. I have a page that will run around 85 smaller queries but if i combine the queries it will go down by almost half. This page is a high traffic page and I don't a complicated query taking up mysql resources while it created a temp table and such. My question is this:

Is it better for mysql to run a lot of smaller queries (ex: simple selects with zero or one join, group by) or one larger complicated query with everything combined.

The thing i have to keep in mind is that the mysql selects are comming off the localhost that the web server is also running on so they share the same resources.

Remote MySQL Connection Slow
I have a website on a dedicated server that was running really slow, so I got another, much faster server and transferred the MySQL database to this server. However, now when I connect to the faster server's database using the slower server's PHP web pages, the overall page loads much slower than when the database was on the slower server.

Any ideas why this would be? Right now the host name is just an IP address. Does it need an actual resolved hostname to be fast? MySQL on the faster server is supposedly tweaked for fast service (my-huge.cnf).

Mysql 5.0 - Using My.cnf - Unix / Slow-query-log
I installed mysql 5.0 and need to set up slow-query-log and other logging options.

Here is what I did. But I dont see it working yet.

1. cd /var/db/mysql
2. chown mysql slowquery.log
3. touch /usr/local/etc/my.cnf
4. chown mysql /usr/local/etc/my.cnf

vi my.cnf

[mysqld_safe]
-u mysql
--log-slow-queries=/var/db/mysql/slowquery.log

so now when I type:

mysqladmin shutdown
and than

mysqld_safe &
my sql restarts but the log files are not being used.
also - how do I know if my my.cnf is being used at all?

Delay, Latency, Slow Mysql
I recently upgraded mysql from 3.23 to 4.0 and found that my websites using the mysql db had a 5 second delay, or latency. I searched for 2 days and found no answer to my latency problem until just a moment ago. I'd like to share the answer in case someone else finds this occurring to them as well.

I was using my real static ip in my website mysql address instead of 'localhost', localhost should have been used initially anyways. I am not real sure what my reasoning was when I initially chose to put in my static IP.

So, If you are like me and keep your stuff on your local machine, use 'localhost' instead of your static IP. At least it fixed my delay issues.

MySQL Slow: Configuration To Blame/fix?
I have MySQL 5 running on a PC, and have grown used to the startling slowness of MySQL joins. The most recent disappointment is a straightforward self-join of a not-too-big (700K rows), indexed (on very-high-cardinality column FILE) table

create table temp2 as
select a.*, b.time as time2,
from temp1 a left join temp1 b
on a.file = b.file
and timestampdiff(HOUR,a.time,b.time) = 1;

that completed in 10 hours. (Indexing resulting table took 20 minutes). If this is not a problem with the programmer, or with MySQL, could it be a result of a mismanaged installation, one that could be fixed with a judicious re-configuration/choice of MySQL options?

MySQL ODBC 3.51 Driver Very Slow!
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL
tables to a Microsoft Access 2003 database. I am finding that the data from
the MySQL tables takes a hell of a long time to load making any kind linkage
with my Access data virtually useless.

I have the MySQL driver setup in as a USER DSN. The MySQL data is sitting
out on a server and the Access database is running locally.

Mysql -s -u Root Bmd < Db.dump2 Slow
I've been sent a dump of a database and am trying to restore it with the
command in the subject line. It's progressing, but very slowly. The
problem is that the database is 40GB.

Now if mysql is doing database-y thing like allowing access while building
tables this might be slowing it a little. Is this the case and is there any way
round the problem and slurp the the whole thing in isolation.

Background: debian sarge

mysql-server-4 4.1.11a-4sarge

I really *am* a mysql newbie - tell me I'm silly for starting at 40GB

My MySQL Back-end Works Very Slow
I have installed my MySQL Server in a Window XP platform running in a Pentium4 machine. When I access the database on the machine, it is working in a normal speed. But when I access the database from a client machine (still running in windows platform), it runs very slow.

VER VERY VERY Slow MySQL Query HELP URGENTLY NEEDED
I have the following MySQL query, but it is VERY VERY slow and seems to be crashing the server. There are 300,000+ records in the 'tracker' table.

SELECT sites.*, SUM(if(tracker.type='view',1,0)) AS numberOfViews, SUM(if(tracker.type='click',1,0)) AS numberOfClicks, SUM(tracker.revenue) AS totalRevenue FROM sites LEFT JOIN tracker ON tracker.site_id = sites.id GROUP BY sites.id ORDER BY sites.domain_name

MySQL Redhat Slow Responding To Connections
I have installed MySQL 3.53 on two RH7.3 and one RH9.0 boxes.
On one of the 7.3 and the 9.0 box, when I try to connect remotely, it
takes about 45 seconds to connect, whereas the other 7.3 box connects
almost immediately.

The fast box has standard install of 7.3 with tomcat added.
The 9.0 box is straight RH9.0 standard install.

I can't find anything in common between the slow boxes, but not on the
fast one.

Mysql Running Slow On Mandrake Linux 10.0 For AMD 64
I'm having a problem with a new machine running Mysql version 4.0.18
on the AMD64 version of Mandrake 10.0. The new machine has got 64bit
AMD processor and 2GB of RAM.

Nearly all queries(updates are especially slow) are running 20/30 or
more times slower than on an inferior(32 bit processor, 1.5GB RAM)
machine runnning Mysql 3.23.56 on mandrake 9.0.

As far as I can tell mysql is configured correctly for the power of
the machine. The my.cnf file looks like this: Code: