Mysql 5.0 - Using My.cnf - Unix / Slow-query-log

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

---

Unix Timestamp Select Query
I am having trouble with the following sql query.I am trying to select all records in the future where the date is greater than today but to include today, the date field in the db is an unix timestamp.


Code:

"SELECT * FROM mysqltable WHERE mysqldatefield > now()-1 ORDER BY mysqldatefield DESC"

---

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.

---

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

---

Slow Query
I have a query that is running really slow !!!!
I have joined on Key fields and indexed the tables fully but it is still solw.
--------------------------------------------

select d.id, a.signed, u.Forename, u.Surname, d.paid, p.date, d.payment, p.amount, d.acctual
from details d
join poten a
on a.id = d.id
left join recieved p
on d.id = p.id
left join users u
on a.signed = u.userid
where d.paid > '01-Dec-2005'
and d.authorrceived is not null
and d.authorrefused is null
and ((d.payment starting 'E' or
d.payment starting 'e') or
(d.payment starting 'Q' or
d.payment starting 'q' and
p.target = '500'))
order by d.paid, a.signed, d.id

---

Slow Update Query
I have about 2000 update queries to do, which takes about 1 hr on 250,000 rows.
My table is getting kinda slow here is the query i am using
UPDATE nametable SET
sectionname = replace(sectionname,'".$oldsec."','".$sec."'),
categoryname = replace(categoryname,'".$oldcat."','".$cat."'), published=Ƈ'
where sectionname='".$oldsec."'
and categoryname='".$oldcat."' ;

I am wondering if the same thing is possible with an insert... on duplicate key statement?

I cant seem to get the insert statement to work, but not even sure whether it is appropriate.

With this query I am basically finding and replacing some columns based upon another table (within the php script I am using)

---

Slow Query Log Files
I have edited my.ini file to create a file called slow queries. My problem is that when the server starts up i do not get a full list of all slow queries. Is there anything else i need to change? How else can i come about in getting all queries that took a long time to execute? im using mySQL 5.0.9.

---

Slow Query Log Not Staying On
I'm running MySQL 4.0.16 on Windows 2003. I just added the mysqld-nt
command line option to enable the slow query log, started MySQL, and the
option showed up as turned on. Then later I restarted the server, and the
slow query log option went back to being turned off. Is this a Windows
problem in not remembering the service parameter? Has anyone else seen
this?

---

View Slow Query Log
I searched here, google and MySQL docs but did not find an answer.
I'm using MySQL-Front from a Windows platform to administer a remote database. The remote server doesn't have phpMyAdmin or anything like that.
MySQL-Front reports 133 Slow Queries and an average of 15 queries per second. But I dont' know how get more information than that.

---

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.

---

Can't Turn On Slow Query Logging
long_query_time = 1
log-slow-queries = /var/lib/mysql/slow_queries.log
Is the above syntax not correct for enabling slow query logging? All examples I've seen have the dashes in the second variable and underscores in the first.

When I restart MySQL with those lines in my.cnf, it fails to start, but writes nothing to its error log.
/var/lib/mysql/slow_queries.log exists, is owned by mysql, and has read/write permission.

---

Turning On Slow Query Logging?
Background: I paid a young admin set me up on a database server. He installed the basic I needed for the server...at my request...No Cpanel...mysql and apache and some tight security w/o even a domain name to SSH into. Unfortunately, he's a busy kid, and teens sometimes don't realize that people depend on them...and well, I can't really seem to get him to do much so I gave up and figured it's a good way to force me to learn all this myself...
Well anyway, now I want to turn on Slow Query logging. But before I do that, I need to know how MySQL is running. Is SQLogging turned on already? Where is it logging to? So first thing I want to look up is, when the server is rebooted, what's the command to restart mysql? No clue. How do I change the setting? And of course, the server is production, so when I make the change, it needs to be quick, it needs to be smooth, and I need to be able to roll back to the previous config if necessary.
I'm running Redhat Enterprise.

---

Help Rewriting A Slow Phpbb Query
I have a "glance" or "Recent Topics" list on my forums that have become fairly complex. I modified an already feature rich glance mod to allow users to select individual forums to exclude from showing topics in the list. As well when users are members of certain forum groups, they see topics from the group forum in the list, and they are highlighted a different colour.

The main SQL query to create the list often is showing up in the MySQL Slow_query log and I'm pretty sure is the main cause for the page loading slow.

I am no mysql Guru, so I thought I would seek the advice of some to improve or totaly rewrite this slow query.

PHP

$sql = "SELECT     
    f.forum_id, f.forum_name, t.topic_title, t.topic_id, t.topic_last_post_id, t.topic_poster, t.topic_views, t.topic_replies, t.topic_type,
    p2.post_time, p2.poster_id,
    u.username as last_username,
    u2.username as author_username
FROM "
    . FORUMS_TABLE . " f, "
    . POSTS_TABLE . " p, "
    . TOPICS_TABLE . " t, "
    . POSTS_TABLE . " p2, "
    . USERS_TABLE . " u, "
    . USERS_TABLE . " u2                
WHERE
    f.forum_id NOT IN (" . $forumsignore . $glance_recent_ignore . ")
    AND t.forum_id = f.forum_id
    AND p.post_id = t.topic_first_post_id
    AND p2.post_id = t.topic_last_post_id
    AND t.topic_moved_id = 0
    AND p2.poster_id = u.user_id
    AND t.topic_poster = u2.user_id
ORDER BY t.topic_last_post_id DESC";
$sql .= ($glance_recent_offset) ? " LIMIT " . $glance_recent_offset . ", " . $glance_num_recent : " LIMIT " . $glance_num_recent;


The "NOT IN" list varies per user, but here is an example:
NOT IN (77,75,76,25,26,37,63,64,66,67,67,1,25,26,37,70,28,75,76,78)

---

Slow Query W/ Join & Ordering
I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below:

It involves two tables, products and vendors.

Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description:

CREATE TABLE `products` (
`id` int(11) NOT NULL auto_increment,
`vendor_id` smallint(6) NOT NULL default Ɔ',
`product_code` varchar(255) NOT NULL default '',
`internal_name` varchar(255) NOT NULL default '',
`lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

PRIMARY KEY (`id`),
UNIQUE KEY `product_code` (`product_code`),
KEY `vendor_id` (`vendor_id`)
) ENGINE=MyISAM;
Vendors are much more straightforward:



CREATE TABLE `vendors` (
`id` smallint(6) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
The following query executes in no MORE than 0.01 seconds:


SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
FROM products as p
ORDER BY p.product_code ASC
LIMIT 0, 30;
And has the following attributes:

+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
| 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+
When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds:



SELECT DISTINCT p.id
, p.product_code
, unix_timestamp(p.lastmodified) as lastmodified
, p.internal_name
, v.name as vendor_name
FROM products as p
LEFT JOIN vendors as v ON v.id=p.vendor_id
ORDER BY p.product_code ASC
LIMIT 0, 30;
It has the following characteristics:

+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+
Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me.

Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so:



mysql> explain SELECT DISTINCT p.id
-> , p.product_code
-> , unix_timestamp(p.lastmodified) as lastmodified
-> , p.internal_name
-> , v.name as vendor_name
-> FROM products as p
-> LEFT JOIN vendors as v ON v.id=p.vendor_id
-> LIMIT 0,30;
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary |
| 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+
Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,

---

Federated Tables Slow? (like 4.5 Hrs For A Query)
I've got a problem with federated tables. I'm using MySQL 5.1 (with InnoDB as the default table type) on a Win2K server, on which I've got four federated tables pointing at four MyISAM tables on a MySQL 4.1.11 server. Of the four tables, three of them run just fine, and I can retrieve data quickly with no problems. The fourth is a sheer pig. While they have different columns, all four tables are roughly as complex as each other, all having the same features and developed by the same team.

The most obvious difference, and what I suspect might be the problem, is that the first three tables have between 150 and 1,000 records, the fourth table has closer to 15,000. Still, there isn't that much lag when I'm pulling from the smaller tables, and the lag is really serious when I'm pulling from the larger one; I ran three queries last night to test, and I could pull data from the smaller tables in about 5 minutes, but the larger table took 4.5 hours- possibly because it was joined with two other tables, but the joins on the smaller tables didn't cause this kind of problem.

The second obvious difference is the fact that I'm pulling from a MyISAM table into a federated table... from which I would like to store into an InnoDB table, but it ends up timing out quite a bit.

Connecting to the database I've federated to isn't a problem. It responds to a PHP frontend lightning-fast. It's just my federated tables that suck so bad. I wouldn't even use federated tables, but I need to pull from the MyISAM database for storing historical records of inventory. What am I doing wrong, and what can I do to speed things up?

---

Slow Inefficent Query (FIXED)
I have this query which pulls members from a table of 1200 members details, filtering out those without and email address and those that don't want Newsletters, and also any whose email address exists in a second table (emails that have hard bounced previously)

My first attempt took an age and returned about 10,000 members, clearly loads of repeated rows

My 'final' attempt with a DISTINCT shoved in works perfectly but is really slow, PHPAdmin says it takes 1.1s but on the website the data doesn't appear for over 30s, all the other sundry queries on the same table work nice and quick on the same website using the same php to generate the table of results

The googling I've done suggests (to me) that there's nothing wrong with the query but I suspect that there's far too much checking of fields going on from the pre DISTINCT query's results ....

---

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)

---

Large Table, Slow Query Question
I have a table with ~800,000 records. I need to grab random rows from the table based on certain criteria. The problem is that average lowest subset to grab the random row is around 200k. Here is what I'm trying to do:

There are 4 columns: data,n1,n2, and n3. I need to get the value of the data column based on criteria using the n1-n3 columns.

The most common query is SELECT data FROM table WHERE n1 = ?

The problem is that n1 can be only 1 of 5 possiblities. When the table is finished being populated there will be roughly 1.5 million records and 250k for each value of n1. Of course, I have an index on each n column.

Right now with just the 800k records it can take over a second, sometimes multiple seconds to run the following in order to get a random row from that subset:

SELECT COUNT(1) AS total FROM table WHERE n1 = 3;
index = random number from 1 to total
SELECT data FROM table WHERE n1 = 3 LIMIT index,1;

How can I speed this up? I need it to take less than half a second if possible. Thank you.

---

Slow Execution For A Left Outer Join Query
Whats likely to be the cause of slow execution for a left outer join query?

The original query joins three tables but even if I narrow it down to one it still takes a long time to execute.

$query = "select distinct materials.* from materials";
$query .= " left outer join materials_products on materials.material_id = materials_products.material_id";

There's 914 rows in the materials table and 1348 row in the materials_products table

Is it likely to take a long time for this amount of data or is there likely to be a problem in the table(s) set up or query?

---

MySQL Via Unix Sockets
I'm using java+JDBC connector. Where do I need to set up something to work with /var/lib/mysql/mysql.sock

---

Two Versions Of Mysql On Same Unix Box
I have mysql 3.23.58 version running on my unix box. I installed a new application on it which need mysql version > 4.0 so I thought of having two mysql versions running on the same unix box. I have first installation in /usr/local/mysql and I untared the new version in /var/local/mysql-standard-4.1.18-pc-linux-gnu-i686-glibc23.

I am following instructions given in http://dev.mysql.com/doc/refman/4.1/en/multiple-servers.html I also created a empty file and named it as mysql.sock.new and stored it in /var/local. When I try to start this new version from /var/local/mysql-standard-4.1.18-pc-linux-gnu-i686-glibc23 using ./bin/mysqld_safe --datadir=/var/local/mysql-standard-4.1.18-pc-linux-gnu-i686-glibc23/data & it says "A mysqld process already exists". Please tell me where I am going wrong and how to configure it properly?

---

Mysql Unix Client
I there, I would like to know if there is a mysql client that we can use from unix to connect to a mysql windows server?

---

Can't Insert A Unix Timestamp Into Mysql
Just upgraded to 4.1.8 and when I create a field with a date value (field is trdate), it always has default of 0000-00-00. And when I insert values with unix timestamp values (which I prefer, and they come from php's strtotime function),the record does not insert properly. I tried to set, through phpmyadmin, unix/current timestamp and to no avail.

If I use a strtotime php function to convert a date into a timestamp, what is the proven way to make sure that value is saved during inserts? I am on the mysql manual, searched this board, but nothing works.

---

Needed Help With Uninstalling MySQL 5 On Mac/Unix
I have MySQL 5 on my Mac 10.4 and I want to completely uninstall it because I think I have MySQL 4 running on it too. I'm not too sure how to check for this. I'm not the best with administrating a database.

---

Calling MySQL In Unix In One Line
I'm running MySQL in Unix and I need to run mysql without ever actually going into mysql. Basically I need to open the database, do what i need to do to the database and exit all in one line of code.

If I have to, I could save the sql in a file and go
mysql -h localhost -u ______ -p _________ << mysqlcode.txt
or something with Unix redirection/piping.

However, I'd like to do something more like
mysql -h localhost -u _________ -p ________ - (use tempdatabase; select * from test;)

Is there anyway to do this. The -e paramater looks like it might do that, but I can't figure out how to use it.

---

MySQL Generate UNIX Timestamp
Cam MySQL create a UNIX timestamp during an INSERT? I want to create a timestamp that is the number of seconds since January 1st 1970. Are there any inbuilt functions for doing so?

---

Invoking A MySql Stored Procedure From C In Unix
I've this problem where I have to invoke a MySql stored procedure from my C program. I searched a lot for this. But I couldn't find any solution to this problem.

---

How Do I Change Which Editor The Mysql Unix Client Uses?
Is it possible to tell the mysql client which editor to use when a user types "edit" at the mysql command prompt? I looked in the mysql client manuals online, but there was nothing about setting editor pref's.

---

Which Is Faster In MySQL? Comparing Unix Times, Or DATETIMES?
I'm leaning towards using unix times inserted into my MySQL for ease of use in manipulating the data with PHP. I think this will be the final decision, which one would be faster?

--

Mysql:
* INT (4 bytes)
* DATETIME (8 bytes)
* TIMESTAMP (4 bytes)

--

My PHP application does alot of querying from the database to compare specific data within TIMERANGES, so always, I'm querying with "time >" or "time <".

My question is would doing the WHERE time query with the native MySQL DATETIME be faster, than doing a WHERE query with the unix timestamp?

---

Search MYSQL Date-range With Unix Timestamp?
I want to search for records that are ON or After a certain date in a column that uses MYSQL date form (yyyy-mm-dd) with a unix timestamp. Can this be done?

---

Search MYSQL Date Column With Unix Timestamp?
I want to search for records that are ON or After a certain date in a column that uses MYSQL date form (yyyy-mm-dd) with a unix timestamp.

---

Import Dump From Unix Mysql To Windows Mysql
I am trying to load a dump from a MySQL database on my ISP's server to my development environment on a Win 2K machine (running MySQL). When I run the mysql command to run the load script, MySQL gives the following complaint:

"ERROR 1064 at line 21: You have an error in your SQL syntax. Check the manual t
hat corresponds to your MySQL server version for the right syntax to use near 'D
EFAULT CHARSET=latin1' at line 7"

The dump from the ISP's server is created using mysqldump. The ISP is running MySQL 4.1.20. My development environment is MySQL 4.0.26. I have no problem loading dumps made from my development environment usng phpmyadmin.

---

Linux Mysql Nfs : Bind On Unix Socket: Function Not Implemented
I have installed Mysql on red hat ES release 3 and i was working great.

For my applications, I decided to to put the data directory on another machine. The other machine is a big disk of storage (Adaptec snap server 1 terabyte)
It working great for all applications except Mysql

In fact, i put /var/lib/mysql on another machine and I made a nfs mount

When i try to start mysql i have this :

/etc/rc.d/init.d/mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL: [FAILED]

in the error message i have this

051028 11:12:33 mysqld started
051028 11:12:33 Can't start server : Bind on unix socket: Function not implemented
051028 11:12:33 Do you already have another mysqld server running on socket: /var/lib/mysql/mysql.sock ?
051028 11:12:33 Aborting

051028 11:12:33 /usr/libexec/mysqld: Shutdown Complete

051028 11:12:33 mysqld ended

I have verified the permission and everything seems to be ok....

---

Creat Mysql In Unix And Pull/post Data From A WindowsXP Machine With A UserDSN?
Can I creat mysql in Unix and pull/post data from a windowsXP machine with a UserDSN?

Is there an Administrative Tool (User DSN) that I can utilize to pull/post data to a Unix account without having to through the long process of "FTP" and "XTerm windows" ?

---

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)

---

Upload MySQL Database To A Unix Server - What Path To Upload To?
I am going to upload a MySQL database to a Unix server. However, what path do I upload to?
Something makes me thing /var?

---

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

---

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.

---