MYSQL :: MySQL Very Slow.

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.

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)

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.

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.

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?

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:

Slow Performance After Falling Back To MySQL 3
Yesterday I have upgraded my linux box with a pair of Xeon 1G CPU and
reinstalled everything (it was a sinle Xeon 700). The default RH9
installation comes with MySQL 3.23.54. After the reinstallation I
found the machine was not as responsive as before. (It's not very
slow, but some lagging is noticed)

I was running Mysql-max 4.0.11 and the performance was ok, except the
slowness during the peak hours, which was regarded as normal. I would
like to know the differences between Mysql 3 and 4. Is the new version
making a significant jump in performance?

I also noticed the system eats up memory faster than before. With the
old setting the system never used up 60% of physical memory, even at
extremely high loading (30+). Now it easily used up 70% of memory.

The server is running Mysql only. It's equipped with 2 Xeon 1G and 2G
of memory. The kernel version is 2.4.20-8smp #1 SMP. Also attached the
my.cnf for a clearer picture: Code:

MySQL Slow To Connect To Remote Database
I was running a PHP/MySQL web site with the database on the local web server. I needed to move the database off the webserver (located in the firewall DMZ) and onto its own machine (behind the firewall on our private network)

Now when I run my web site it goes really slow. I ran mysql from the command line on the web server and connected through to the database machine behind the firewall using
webserver:/# mysql -h 192.168.1.7 -u www-data -p

After I enter the password, it takes around 5-7s before I see the mysql> prompt.

Is mysql trying to do reverse dns on the IP address and timing out? I added the machine details to /etc/hosts so it would be recognized but no improvement. Or do I have something wrong with my mysql configuration?

MySQL Slow With 4GB Memory - Tuning Required?
I am using MySQL 4.1 on Linux (IBM Open Power Server). The Server has 4GB memory, 380GB hard drive.

I have a Java based application that runs in Tomcat in a Windows environment using SQL Server. As part of a customer project I have got the application to work on Tomcat on Linux with MySQL.

Both databases have a large amount of data in some of the tables, e.g. 500,000+ records in some of the tables. Some of the tables have indexes on them.

There is a specifc part of the application that is slow when running against MySQL (10 seconds to write a record to a table with 500,000+ records in it.) The same bit of functionality running against SQL Server is instance, e.g. 2 secomds max. The code is doing an insert statement. Once the insert has taken place we need to get the ID for the inserted record. This is using SELECT MAX....... The ID column is set as the Primary Key and is indexed. Code:

User Admin In Mysql-administrator Slow On Linux
We have a MySQL 5.0.16 server installed on a RedHat 8.0 machine.
mysql-administrator v1.0.22 is installed on RH Linux FC3 from the
mysql-administrator-1.0.22a-1.rh9.i386.rpm.

Everything seems to run fine except User Administration is *extremely*
slow. If I click on "User Administration" in the sidebar, it takes
about 2 minutes (seems like forever) before the window comes back with
the infomation. All the other functions seem to function fine.

Is it because I'm using mysql-administrator on a remote host? Do I need
to move and rebuild the server to a newer Linux version? mysqlcc
doesn't have this problem at all. (It's using the libraries from MySQL
4.1 that the databases were migrated to.)

Slow To Get "C:/mysql>" Prompt
I'm setting up MySQL, Apache and PhP on my pc - Win98, 128meg to work on a project.

My problem is this - I have installed MySQL and find that if I go into it via the DOS prompt (C:/mysql/bin/mysqld) it takes at least 10 minutes before the C:/mysql> prompt appears and I can start using it ... this I'm sure is not right ? I have tried with the mysql admin tool but it too takes ages to load ???

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.

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 ?

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

Slow Connect
Does anybody know why it sometimes takes more than 10 seconds to connect to a database and sometimes it just takes half a millisecond?

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 ?

Slow UPDATE
I have a table with the following structure;

CREATE TABLE my_table
(
id_1 int(11) NOT NULL ,
id_2 int(10) NOT NULL ,
stauts tinyint(1) NOT NULL DEFAULT 0 ,
PRIMARY KEY (id_1)
) Engine =InnoDB';

The table currently has arround 100,000 entries. When I try to run variations of the following statement it is taking around 4 seconds per query;

UPDATE IGNORE my_table
SET id_1 = 74240, id_2 = 5

I need it to be running a lot faster than 4 seconds per query as I need to update upwards of 100,000 records a day! My server is fairly beefy, a 3 gig dual core opeteron and is generaly running below 1.0 load.

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?

Very Slow Select
The line indicated below from my php script is very slow (about 10 seconds). I have this field indexed so I thought that it would be much faster. Could someone tell me what might be wrong?

I'm also including the dump of the table definitions. This is a cd cataloging database.

Right now the filenames table is empty and I'm trying to populate it, but at the rate it's going it would take days. I have about 700,000 records in the 'files' table, but none in the 'filenames' table yet. Code:

Slow Running Sql
i am not using mysql but an unknown database system on a unix box - i have no control over the database but have purchased an odbc driver that seems very 'clunky' after using mysql - this is an sql statement question rather than a mysql tech question. if i run this:

SELECT
MK_01_vehicleRecords.registrationnumber, MK_01_VehicleRecords.vehiclenumber
FROM MK_01_VehicleRecords
WHERE (MK_01_VehicleRecords.vehiclenumber = '36176')

Slow Connections
I am using MyODBC-3.51.11-2-win on Win 2003 OS. I am not able to see all of the connections in the list under the System DSN tab. The connections that show allow the ASP pages to run at an expected rate.

However, the ones not showing in the list are running extremely slow. If I attpemt to recreate the connection I am told that the connection already exists and asks if I want to replace the existing connection. Wheter I click yes or no the connections do not show and the pages run slowly. How do I get them to show or resolve the issue. The ASP code is the exact same SQL statements and connection strings as the in previous applications.

Slow Restore
Mysql 4.1.15 on Win2k. Using InnoDB.

Using the mysql administrator gui to create a backup, everything goes
fine, and restores quickly.

Using the command line:

mysqldump %dbname% --single-transaction > %dbname%.sql

creates a file about 15% smaller than the gui produces, and is
EXTREMELY slow to restore. I have tried adding locks, skip opt,
everything. What does the gui use for a command to create this dump?

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.