MYSQL :: HEAP So Slow

Heap Vs InnoDB Vs MyIsam
I'm trying to improve performance of our website, the boss is complaining that its too slow (though it is faster than a lot of sites). We experience particular slow down during updates - which are done periodically throughout the day. We have nearly 2 million records that are being searched and theres a lot of information there to pull back.

Currently all the tables are myIsam, and the total size is around 1.5Gb but the tables being searched probably total 600Mb. We're currently using all 2GB memory at high loads.
I tried converting the main search table to InnoDB but that just made everything horrendously slow - I also heard that it limits to 200 inserts per second whereas with myIsam we're getting around 1k - 1.5k per second.

Could the slow down be due to not converting the linked tables to innoDB? Or just lack of caching?

I now HEAP is supposed to be super fast for selects, how is it for inserts? Do you think 600Mb database would be ok to be loaded into 2Gb memory and still cope with high traffic loads (relatively high - approx 500 simultaneous users)

Or should I stick with MyIsam?

View Replies !

Flush A Heap Table To Disk
For example if I have data that I want to be inserted in to a heap
table (for optimisation reasons) but I don't want to risk losing *all*
the data if mysql crashes. Is it possible to perform something like a
flush so that if mysql does crash some of the data is saved.

View Replies !

Flush A Heap Table To Disk?
As the subject says, is it possible to flush the contents of a heap
table to disk?

For example if I have data that I want to be inserted in to a heap
table (for optimisation reasons) but I don't want to risk losing *all*
the data if mysql crashes. Is it possible to perform something like a
flush so that if mysql does crash some of the data is saved.

View Replies !

ORDER BY W/ HEAP Table Type
I am creating an online application that lists the last 50 users that loaded a page.

I used a InnoDB table type for storing most information (which rarely changes), but was thinking of moving the UPDATE and SELECT function that tracks who last clicked something to a HEAP table for speed.

I think I would be OK with the UPDATE to a HEAP table, but I have read that ORDER BY cannot be used on HEAP indexes. But, do I have to use an Index?

Can I make a SELECT like this on a HEAP table that has only two columns (login and datenow)?

$queryonlineusers="SELECT login FROM mmih_users ORDER BY datenow DESC LIMIT 50";

View Replies !

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.

View Replies !

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 ?

View Replies !

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:

View Replies !

MySQLdb Slow
I have a Python program that parses a file and inserts records into a
database with MySQLdb. I recently upgraded to MySQL 5.0.8, and now my
parser runs *really* slow. Writing out to CSV files is fine, but when I
try to insert the same records in a MySQL5 database, it slows to a
crawl. Using MySQL 4.1 seems fine. The data seems to be inserted
correctly, it's just really slow.

View Replies !

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?

View Replies !

Too Slow Queries
I am using version 5.0.4. I noticed that suddenly my queries were becoming too slow. I have data in three tables, with 40,000 rows, 50,000 rows and 70,000 rows respectively. I was able to run queries that joined the three tables together and get the results in less than 5 seconds. I tried updating the tables through a program that used a stored procedure to delete the tables but I had some other problems. Since the I restored the data in the tables from backup databases. Now I can't run any queries that join the three tables together, it waits for a long time and then times out. The data in the tables look good. Now I can't get the results from any of the other backup databases either. It look like the entire MySQL server has slowed down greatly. I tried rebooting the server to no avail.

View Replies !

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)

View Replies !

Slow Db Access
I have worked with a few mysql dbs on different servers but i have recently been asked to work with one on nicnames. It seems horribly slow. Working in phpMyAdmin (which i had to install myself) it takes ages when i want to do anything. View the table structure, view the data..etc. Any way i can test the speed so that i can compare it against another server i work with and proove there is a speed issue and take it to nicnames cus it is crazy and is going to affect the speed of the website!!

View Replies !

Slow Insertions
I have a MyISAM table with fixed width rows (ie no vars). I also have keys disabled. I am running a huge data load process. When it first started it was inserting about 38 records a second. Now, after about 200,000 records, it speed is about 22 recoerds per second. Whats going on here? and how can I improve it?

I am loading about 15 million records, so the difference between 22 per sec and 38 per sec is SIGNIFICANT (ie 2 days).

View Replies !

Slow Performance
On my index.php page, I have a simple query that checks the session_id against a table where I store other session_id'. If it's not there, it records it (unique hit). If it's there, it doesn't record it (not a unique hit.) This usually goes off without a hitch, and every month or so I empty the table.

Right now I only have about 2500 rows, and it's taking forever to load the page. Is there something possibly server related that could be causing this? My host charges an arm and a leg just to see if there's something wrong if I bring up an issue, so I'd like some insight as to whether there's a commonly known server-side issue that can bog down performance.

View Replies !

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?

View Replies !

Slow Subquery
Can anyone tell me why the following query with sub-query takes forever to finish? (I've le it run for 20 minutes, and it still hasn't finished)

select date from temps where date in (select distinct date from observations where camera like "a")

The sub query returns 10 dates. The outer query is on a table that contains about 40,000 rows. What's the big deal here? All I'm trying to do is select rows from "temps" that match a small range of 10 dates. Is there another way to do this? Is a sub-query the wrong approach?

View Replies !

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

View Replies !

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?

View Replies !

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

View Replies !

Slow Subqueries
I know (by internet) that mySQL is very fast. Problem is that my subqueries that are very fast in Access or SQL Server but they are very slowly in mySQL - since I have to restart my computer because mySQL freeze all the processes. (the resources where used at maximum - 2 Gb RAM, 2,5 Ghz processor)

This is the query :
SELECT NPL, PP FROM P_A INNER JOIN ACTIONS
ON P_A.NA =ACTIONS.ACT_N WHERE P_A.NA in
(SELECT NA FROM P_A WHERE NP ='ABC')

P_A has 5 columns and 12000 rows
Actions has 5 columns and 770 rows

Any suggestions ?

View Replies !

Slow Request
I want to run the following request:

mysql> select count(*) from fingerprints where fingerprint in (select fingerprint from fingerprints where id_file=3263);

where fingerprints is the following table: ....

View Replies !

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 ?

View Replies !

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.

View Replies !

Slow Max() Selection
I have a large table with some fields. Two of them are:

year char(1) && year's rightmost character
OrderNo integer

Both are keys (BTREE).

I need to select the last order in the year.

select max(OrderNo) from ORDENES where year='8'

While the year is growing the selection speed id decrement (12 seg).

View Replies !

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?

View Replies !

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.

View Replies !

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.

View Replies !

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?

View Replies !

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

View Replies !

Slow Update
The following query can sometimes take up to 2.5 seconds to execute on a table with only 150,000 records.
UPDATE items SET item_views = item_views + 1 WHERE id = 5897;
is there any way I could speed this up? Some setting I could change to make MySQL faster for this?
The field "id" is the primary table key.

View Replies !

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)

View Replies !

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.

View Replies !

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

View Replies !

How To Log-slow-queries
I'm running MySQL 5.0.

Now I would like to log-slow-queries. With MySQL 4 I used

mysqld --log-slow-queries --log-long-format start

but now I get following note:

mysqld: Too many arguments (first extra is 'start').
Use --help to get a list of available options

It looks like starting and stopping now only works with

etc/init.d/mysql start

View Replies !

Service Pack 4 Slow Down
I'm running Windows 2000 Server and MySQL 4.1 and all has been great
until yesterday when I installed Windows Service Pack 4. Now when I
query a database it displays very slowly. Before the service pack it
was almost instant.

Everything works properly, it's just very slow.
Anyone heard of anything like this? If I use the MySQL command line and
query the database it's very fast. The slow querys are from php pages.

View Replies !

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

View Replies !

Slow Database Retrieval
iwhat would be the cause of our problem? i don't thnk it has someting
to do with the way i run my queries from my applications since i do
not encounter such problems on our old machine..Also i tried to
execute queries from my sqlyog :
ex:
select * from db_town
result: 8902 rows() in 2687 ms
where on the old server it should take only about 421 ms

select * from diagnostics_detail
result: 42499 rows() in 27609 ms
where on the old server it should take only about 4375
ms.

View Replies !

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 Replies !

Slow When Using Desc Clause
I'm scrolling by Order_# where I find and display on screen the < (lesser
then) value of Order_#. Here's the syntax:

"Select * From Orarchd Where `Order_#` < " & lngOrderNumber & " Order By
`Order_#` Desc".... Order_# is the primary index.

lngOrderNumber is the current value of Order_#. Here's the problem ...it
takes forever to find the next record (at least 10 seconds)

View Replies !

Extremely Slow INSERTs
Im inserting about 4,500 records into a table. There are only three fields each containing text.

Im using PHP to insert these records, but it's REALLY slow (can take like 5 minutes to insert line by line. I also tried generating a SQL file and inserting like that, but it still takes a couple of minutes.Is there anythign I should be checking in the mySQL config or something to make this faster??

View Replies !

Connect To A Database Is Slow
I can connect to the mysql server really fast. But when I select a database, the server will wait for about 10 seconds before connecting obviously, imagine that kind of wait on a website. loads of users will click off.

View Replies !

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.

View Replies !

Innodb Fast, Then Very Slow
I have a massive table that I converted to innodb. It made a huge speed difference in my site because tables locks basically stopped happening.

However, after about 10 minutes mysql slows to a crawl. All queries seem to take forever to run, like 20-30 seconds each. If I reset mysql its all fast again for another 10-15 mins.

This only happens when I have that one table set to innodb. When everything is MyISAM the database speeds are consistent, I just get a lot of table locks (which I'm trying to reduce).

View Replies !

Server Performance Slow
I have designed a service on my site which requires about 30 -40 SQL queries per page to run. Everything works without problems apart from the slowness. The page will load very quickly until it gets to the first query, then it pauses..... and carries on slowly producing the results. (When I say slowly, I mean relative slow compared to the speed this forum loads for instance)All the other web apps with mySQL work without slowing down, but they don't take up as much resources. Even the VB board is very fast.
I'm running a 2.8Ghz 1GB Win2003 server, so I doubt that is a problem.
Is there a guide to optimizing mySQL server or a key "setting" I should be changing?

View Replies !

PhpBB Slow Loading
my site runs quickly except when a user hits forum (allready signed in or logging in) to login to the forums it takes 10x as long to get to the forums as it does the rest of the site, once in the forums its ok and runs as normal,is there any settings in SQL that can speed this up? or is there another problem.

View Replies !

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.

View Replies !

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?

View Replies !