MYSQL :: Performance

Large Table Performance Problem With MYSQL Performance
I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results.

Environment
Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram

mysql 4.1 with odbc 3.51 MYISAM
windows 2003 server std edition
date, account and invoice number are indexed
Database size 18 million rows

I am querying (selecting) columns of a date and an account

our tester program that opens a socket to the Mysql database and does a select for the above n times
each time the date and the account is randomized to minimize hits on records closeby.
This program will perform over 1000 queries per second.
At the end, the Mysql socket will be closed
Even when the socket is closed each time, I still get 400 queries per second.

When I enter a similar query manually a web interface, I get about 3-9 second response time. This program opens/closes a socket for each query when using EMS I get similar 9 second results.

Does anyone have any suggestions Also in production, this table will be accesse for both read and write will I have problems. My testing showed that Innodb is much slower.

Performance Of LIKE
I'm weighing the performance of materialised path vs. nested set algorithms, for storing a tree structure. The tree will have a lot of searches, a fair amount of inserts, but probably fairly few move-operations. The decisive factor seems to be the speed of a LIKE-query. I know, that regular expressions perform pretty well, as long as they are anchored. From this, I deduct, that LIKE must be able to perform equally good, since a path is indeed anchored. The questions is, if a LIKE will always result in a full scan, or if it can utilise indices? If it doesn't allow use of indices, would it be worth it, to use an equality comparison, on a substring (Which is essentially, what I'll be querying for) or am I just entirely missing the point?

Performance
I'm developing a PHP script and using MySQL.

What is better, have one database with many tables por all accounts (example: account1_table1, account1_table2... account2_table1....) or one database for each account?

Is there many differences in performance?

Which Is Better In Performance
country

(id) country
(1) America
(2) Germany
(3) Canada
(4) China
(5) Egypt
(6) Russia
(7) Korea
(8) France
(9) Brazil

city

(id) city
(1) Berlin
(2) New York
(3) Cairo
(4) Paris
(5) Seoul
(6) Peking
(7) Chicago
(8) Moscow
(9) Denver
I have two tables like the above.

And I have one of these tables below.

message1

(id) (city) message
(1) (2) I love New York.
(2) (5) Kumkang mountains are beautiful.
(3) (4) I was born in France.
(4) (6) Great Wall is great.
(5) (7) Chicago is a big city.

message2

(id) (city) (country) message
(1) (2) (1) I love New York.
(2) (5) (7) Kumkang mountains are beautiful.
(3) (4) (8) I was born in France.
(4) (6) (4) Great Wall is great.
(5) (7) (1) Chicago is a big city.

code1

select city, country
from message1
left join city on city.id=message1.city
left join country on country.id=city.country

code2

select city, country
from message2
left join city on city.id=message2.city
left join ccountry on country.id=message2.country
I think The codes above have a same result.(sorry, I didn't test it.)

Performance
I have a "performance" related question.

I am developing an events calendar with php and mysql. In the calendar I want any day that has events to be a link. Therefore just before a day is added, I want to query the database to see if any events occur on that date.

Is it bad practice to query the database that much? In this case it'll be hit 30 or 31 times. also, what takes up less space in the db... the mysql date data type or the php timestamp?

Performance
I have 3 tables: books, images and books_xref_images (with the xref
table merely matching up bookIDs and imageIDs.

Assuming I only want information on one book (and it's image) would
it be quicker to do 3 LEFT JOINs in one query....

.... or 2 distinct queries, with the first retreving the book info and the
second getting the image info?

Best Performance For WHERE LIKE AND ..
I'm doing heavy searches on a table with statements like

SELECT * FROM thetable WHERE col1 LIKE '%ab%' AND col1 LIKE '%cd%'

.. and so on. I need to search for keywords shorter than 4 characters, so I can't use fulltext MATCH-AGAINST. When searching using WHERE-LIKE -method above, should I use FULLTEXT or INDEX in the column in question?

Performance
i am having a problem at the moment with my pc. i keep getting performance issues when running mysql queries and when i go in to task manager
mysqld-nt.exe is always quoted as a very high number on cpu.

is there something i may have done incorrectly to cause this.

i have been using php/mysql for a good while now and have never came across this problem before?

it is the same regardless of which queries i am running (including those which always worked fine before).

Best For Performance
It's a web application, accessed via internet, with around 100.000 clients and
each client have around 10 users. This web app have a database of around
100 tables.

Ok, the question is: Is it better to have each client with it's own catalog on
the database (each catalog with around 100 tables) or is it better to use one
catalog for all the clients (the tables would have a lot more records this way,
but only one catalog)?

I'm considering performance.

Performance
I have problems with mysql-performance. When I check "Status", there is written: 23% of all questions are "change db". What does it mean? Select Task are only 13%. Could this be the reason of performance problems?

Performance - Select *
I'm trying to optimize an application, which is putting way too much load on the rdbms. Currently most queries select *, but I'm wondering how much it matters in terms of performance to specify the exact number of columns needed ? Obviously, I'd transfer less data from rdbms to application layer, but apart from that is there anything to archieve ?

Performance Troubles
I'm having some trouble with optimising my database queries. I've recently been making use of a feature that my host provides that lets me create reports on any queries that are taking more than 1 second to generate. Quite honestly I'm not sure if it could be the table structure, indexing issues or just the overall query that's horribly coded.

I've attached the query report from the server in a post attachment. As you can see I like using my inner joins, hehe.

Here are the table rows, in case you're wondering how big tables are relative to each other:

archive - 428,720 rows
blo - 3,741 rows
users - 3,945 rows

It might be useful information.

So yeah, basically what can I do, if anything, to get these queries to move along. Points would be awesome.
:-)

Performance Question
CREATE TABLE __testpages (
rowid int(11) NOT NULL auto_increment,
user varchar(255) NOT NULL default Ɔ',
pagebyurl varchar(255) default NULL,
timebegin int(10) NOT NULL default Ɔ',
pagenum int(5) NOT NULL default Ɔ',
lastpage tinyint(1) NOT NULL default Ɔ',
day char(2) default NULL,
month char(2) default NULL,
week char(2) default NULL,
quarter char(2) default NULL,
year varchar(4) default NULL,
PRIMARY KEY (rowid),
KEY user_2 (user,`year`,`month`,den),
KEY user (user)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

and I genarate 15,000,000 rows. Then I execute the query:
SELECT COUNT(*) FROM __testpages where user='xxx'

the user index cardinality is 11

this query returns between 17,000 and 11,000,000 rows depends on the 'xxx' and takes between 5 and 20sec on my mashine. Is it normal or it is too slow?
I am running the mysql server on dedicated server I don't know exactly its parameters

Low Performance With Linux SMP
I use
#mysql -V
mysql Ver 12.21 Distrib 4.0.15, for suse linux (i686)

with a SMP-kernel

When I used this version with a kernel not SMP mysql was very very fast but now
with the SMP-kernel the mysql is very very slow. I takes several minutes to make an INSERT on a table with near 50.000 Records

Is there any problem with mysql an SMP? is a problem of the version I have installed?

Performance Questions
My company make several flash-based games, and I use php to communicate
with
mysql to provide highscore-lists.

My problem is this:
When I save a player's score in the mysql-table, I want to find which
place
the player got with his score (today). To get this I have tried two
different solutions, which both works, but are very ineffective: (The
Time-field is a DateTime type, and I have Score and Time as Indexes.
The Score-field is a DECIMAL (20,2))

1) SELECT COUNT(*) FROM table WHERE Score>=$score AND Time>CURDATE().
- or -
2) SELECT Score FROM table WHERE Score>=$score AND Time>CURDATE().

.... $place = mysql_num_rows($result)

Both give the right result, but the problem is that when there are many

players playing at the same time, and the table consists of several
million
records, the query is just too heavy for the mysql-server, and it
breakes
down.

So my question is: Are there any better ways of getting a player's
place?

SET Datatype Performance
I have a â€œlittleâ€ performance problem using the SET datatype.

I have a database with over 800,000 rows which have 34 columns which are enum(â€˜0â€™,â€™1â€™) to describe various categories.

So when I search for a certain category or categories the search would be like:

Select count(id) from my_table where cat1=â€™1â€™ and cat10=â€™1â€™ and cat25=â€™1â€™;

If cat1 is indexed and cat10 is not.

Select count(id) from my_table where cat=â€™1â€™;

would return data in less than a sec while

select count(id) from my_table where cat10=â€™1â€™

might take over 5-6 secs to return the value.

Since I canâ€™t index all 34 category columns, due limitations of how many indexes I can create (there are other fields that required index besides the categories) I looked into how to do this more efficiently. I came up with the idea if using the set datatype. I created a test table

CREATE TABLE set_test(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
cats SET('cat1','cat2','cat3',â€¦â€¦'cat34')
);

I loaded it up with 800,000 values from my main table and searches like

Select count(id) from set_test where cats&1;

Would return the count in less than a second.

So far so good. So I added a new column to the main database with the â€˜catsâ€™ column, containing the same info I had in the set_test table. To my surprise:

Select count(id) from my_table where cats&1;

Would take over 6 secs to return the value.

Or course the table has 45 columns (including the old 34 category columns) instead of two, but does it really make that much of a difference? If so, what can I do to get a decent performance?

Mysqlcheck Performance
I launch the command line:

"mysqlcheck --repair dbname" over a database with only one myisam
table of more than 10.000.000 rows and 5 GB. The elapsed time to
repair is about 1 hour!

Is possible to increase speed?

MySQL Performance
i'm running a webserver with the following configuration:

P4 2,4 GHz | 1 GB Reg. ECC RAM PC266 | 2 * 18 GB SCSI RAID 0
Apache 1.3.28 (mod_ssl, mod_gzip) | PHP 4.3.2 | MySQL 4.0.12

This is the current server usage (200 parallel visitors):

9:57pm up 4 days, 1:45, 3 users, load average: 5.84, 5.48, 7.16
393 processes: 392 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: 14.2% user, 85.7% system, 0.0% nice, 0.0% idle
Mem: 1031008K av, 1017828K used, 13180K free, 0K shrd, 29856K buff
Swap: 1044216K av, 248428K used, 795788K free 630368K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
18508 mysql 15 0 326M 116M 14492 S 43.7 11.5 7:11 mysqld

It seems to me to be a too high load for this hardware configuration.
Sombody an idea?

Performance Enhancements
I'm a relative newbie to MySQL, my main role is network admin/management, we have a bespoke database system, that has been designed for us, it unfortunately has a fairly bulky FoxPro (C++) front end on it and within our local network runs fine, and even externally to our VPN based offices, the performance is also very good - what I would like to know, is what can i do to ensure superb performance all of the time, what monitoring tools can i use, and what resources are out there to further enhance it's stability and speed...

Also, are there any other good resources people may know of to actually convert this client/server based application to Web Based, i am a web developer in my spare time and work time, and as such have developed one or two database driven web applications....I feel that moving to a solution such as this could provide unparalleled expansion capability, whilst further enhancing the speed and stability of the application...

Performance MySQL With ADO VB
I'm developing an VB application which uses the SAX parser and ADO VB to
insert.update data in in MySQL. The perormance is very bad. If I use the
same data and insert/update to a MS Access database it takes 14 minutes. But
using MySQL it takes about 5 hours! Here's the piece of code which takes the
most time. Does anyone have an explanation for this? Code:

Disk Performance
We have an application which tends to get very disk-io bound, spending large amounts of processor time in "iowait" during select queries.  Given that writes are relatively infrequent, which would the experienced admins recommend for the disk configuration:

Raid5 or Raid10?  Raid0 is even an option, since we have a backup server and could easily add another, but it's not preferable unless the read performance would increase substantially.

The hardware looks like this:

Dell PowerEdge 6650, 4x2.8ghz CPU, 12gb RAM, 4x36gb internal drives, raid 5
Powervault 220S, 14x73gb 15K drives, dual-connected to the server.  Current configuration is Raid5.

MySQL 4.0.18, RHEL3 (2.4.21-27.0.1.ELsmp) and now using the megaraid2 driver.

There is probably room for optimization in the application, but that's currently out of our control.  We may even move to a different database platform, but if MySQL can handle the load I'd just as soon not.

MySQL Performance
I'm facing a performance issue, I'm using JDBC to read rows on a huge mySQL database 1.5 Million rows.

The programme basically read rows by sample of 1000 rows.

select * from <tbl_name> LIMIT i, i+1000

and print result in a text file.

This takes 5 minutes for the first 500000 rows, 10 min for the following 500000 and 18 for the rest. Which seems to me very slow just for reading rows?

I wonder 2 things:
1- is this normal for mySQL to take this time?
2- if not, is using LIMIT this way in the SQL would have an effect on mySQL performance?
2- how can I improve this performance.

If you think of anything that can improve the performance on a select * from , please let me know.

SELECT WHERE Performance On LIKE Vs =
I have a table:

CREATE TABLE file
(id VARCHAR(10) PRIMARY KEY,
image MEDIUMBLOB);

The table is filled with 4073 rows with images of 50-100k each.

When I do a SELECT query using a WHERE-clause I get a huge difference
in time before the result is delivered:

SELECT image FROM file WHERE id=1234567801;
1 row in set (3.73 sec)

SELECT image FROM file WHERE id LIKE 1234567801;
1 row in set (0.02 sec)

Why is this difference in times?

MySQL Performance
Our database contains about 27,000 records only and it is very slow. Can someone please suggest any tips to increase the performance? We used InnoDB.

Performance Vs Many Records
I currently have 300 records in table "metrics". These metrics are assigned to users. So each user has access to the 300 metrics. by default, my SQL will load all 300 on a page for the user.

I am now wanting the users to be able to say which they want to appear. So, out of the 300 they can tick the ones they want to see.

What's the best way of doing this?

I have thought about having a table called metrics_to_user which would store the user ID and a whole list of the metrics they choose. By default, every user would then need 300 records in this table? then they untick ones they dont want to see.

Or another way is just to store just the ones they DONT want to see. So when I am loading the page, I do some SQL to get all 300, but somehow forget the ones they dont want.

Performance Of Views
I have a large table in a database that takes a long time to do any queries on. All user queries on this table are done as part of a background process at the moment.

The table holds transaction information from organisation and account id's; each organisation may have more than one account id. This table contains the raw transaction data, so there are many 1000's of records for each id. The table is keyed on customer number, which is different from organisation id and one customer number may have more than one organisation id. Code:

Performance Question
I have 2 questions regarding performance:

1) I'm building a monitoring system that has to store lots of sensor
data that I 'll have to query from time to time. I have pressure and
temperature. Since we sample every 500 ms we will get lots of data
after some time. Will my performance increase by making 2 tables; one
with pressure and one with temperature? Thus when querying (eg
pressure) mysql will only need to look in the pressure table that
contains half the data opposed to when querying a table that contains
pressure and temperature (and is double the size)?

2) I have read that when querying the server in read mode opposed to
read/write mode you can get a performance increase. Can this be done
with php?

(View) Performance
I have a huge table with product data from different shops in my database. There is a flag column 'import' witch flagt products with 1 for my PHP script.

To select from one shop all product individually, work with it, and then update these product import flag to 0 in the database, seems to be the slowest way. I thougt about a view about all products of a shop with flag '1'. Code:

Performance Question
I want to store every second about 10.000 data entries (time as long, two double values, 3 booleans) for a period of about 2-3 months.

Can MySQL handle this amount of incoming data (without clusters)?
When not, are there other options?

When the database increases over time, what about the performance of queries?

And the last: Is there a possibility to write back the data per day without the loss the possibility to query also data from within those days in the past?

Mysql Performance
The case is, i need to create a website with about a quarter million records
database.

I'm not sure about the mysql+php performance.

The other option would be asp.net with sql server 2005.

Could anyone give me your opinion about using mysql+php in this case, is it
worth?

Performance Question
I have a situation where I'm retrieving information from two tables. For one of the fields, nulls are being returned, which is fine. The thing is I want the rows with null in it to appear at the end of the query result. This was easy enough to do.

I came up with two sql statements actually.

The first used a union between two select statements.
   Grab all rows that f1 is not null
   union
   grab all rows that f1 is null

The second used a single select statement, where I used an IF(exp1,exp2,exp3) to set an extra column to 0 or 1, depending on if f1 is null or not. Then I sorted by that column to put the nulls at the bottom.

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.

MySQL Performance
We ran MySQL (4.0.15-standard, 64-bit, SuSe 8.0) benchmarks
on 2 CPU and 4 CPU Opteron machines. Mostly we tested insertion
times with many simultaneous connections (hundreds). To our
surprise we did not find significant differences.Each of our servers (2 and 4 CPU machine) has one 1Gbit network
connection. I have feelings that the machines are the network
bounded, not the CPU bounded. Could it be true? How to verify it?

Replication Performance
I need small clarification on Replication. Is there any performance hit on web apllication while replication process is going on. if, then how to increase the performance of replicating the data from master to slave while application accessing slave database.
Actual Scenerio:
I am having MAS (MySQL database) as Master database(INNODb storage enginee) and SLA (MySQL database) as Slave database (MyISAM storage engine). Now if any records (even single record) comes into master than at the next second it has to reflect in the slave.
My need IS: When replicating the data into slave, my web application should not slow down. Is there any way to acheive this task?

Mysql Performance
I cannot find MySQl performance info at linux "top" command. I already
tested on slow MySQL query that took more than 5 minutes (create
fulltext index), but during the 5 minutes, i cannot find any mysql
process at top command. Below is the system information:

Linux version: 2.6.9-1.667smp (Red Hat 3.4.2-6.fc3)
MySQL version: 4.1.11 (source installation)

Balance Performance
My computer is AMD athlon 64 3400+ with 1.5G DDR. when i execute the
following sql in mysql 5.XX.
select c.filename, c.topic from articles c where c.topic in (select
a.topic from articles a group by a.topic having count(a.topic)>1) order
by topic;
It eat up all the cpu power , and the mysql server stop responsing
until end of this query. Even my apache stop responing too. How to fix
this? How can i set the CPU limit of each query connection?

Performance Needed
I have two tables. One for products and one for reviews.
I have setup a detail page which displays the various fields out of the products table and then looks for all reviews in the reviews table which match the products key and display the average. All good so far.
I want to have a page which displays a whole list of the products and show their rating next to them but I'm wondering if making it search the reviews table and calculate the average for 50 products in one go is going to cause a bit of a strain on the server?
Is there another way I can do this, perhaps storing the precalculated average in the products table but how would I go about doing that?

MySQL Performance
I have a web application which uses MySQL and I wanted to check out the performance of the database.
I have been trying to use JMeter to test the performance of it but I was wondering if there is anything MySQL specific which can give me statistics on performance etc.
Just wondering what other devshedders do

Execution Performance
Just out of curiosity, does adding LIMIT 1 to the end of single row UPDATE queries improve execution speed at all?

Mysqlcheck Performance
I launch the command line:
"mysqlcheck --repair dbname" over a database with only one myisam
table of more than 10.000.000 rows and 5 GB. The elapsed time to
repair is about 1 hour!
Is possible to increase speed?

InnoDB Insert Performance
I was aware that innodb inserts were not as fast as myisam but on my server I think they still take too long. Simple insert/update commands take between 20 to 40 milliseconds. This would imply about 30 per second if that was the only thing the server was doing which doesn't sound right to me.

As a test I created this ultra-simple table with one int column and inserting a value in it still takes 10 millis every time:

mysql> create table test (n int) engine=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into test values (1);
Query OK, 1 row affected (0.01 sec)

Is this actually typical or is my server misconfigured. If so, how do I debug this? What variables should I look into?

Performance Char Vs Text?
I wonder if anyone with a better grasp of databases, well MySQL to be precise could advise me on something.
In one particular column on a table I need to be able to store up to 500 characters, my question is from a performance point of view would it be better to use a text type for this column or split the column into two and use two char columns?
Just a couple of things in relation to my question, I wouldn't actually need to do a search on the column(s) so splitting them in two wouldn't be a problem in that regard and every other column in the table is a fixed format which as I understand would mean improved search performance for the table ( assuming I used two char columns rather than one text column ).
Or is the performance difference so small it is not worth worrying about? ( the table will eventfully get reasonably large, hundreds of thousands of rows, maybe over a million )

Performance Of A Database With A Lot Of Tables
I am working on hosting solution for a popular open source script, where users can sign up and get an own forum. We use InnoDB, and each user has an own set of tables (around 30); there are currently around 500 users registered. Despite the large count of tables, database is small, around 50 MB.

Server is dual Xeon with 4GB of RAM and MySQL uses all memory, available on the server, so there's even nothing left for Apache.

As the user base is growing, what steps would you suggest me to do, in order to keep good database performance and make it more efficient on RAM usage? The former is more important.

Ajax Performance With Mysql?
I'm trying to tweak the performance of a php script, but I don't understand how mysql handles connections, so I'm not sure if I should use standard sql statments, prepared statements or stored procedures.

The php script is called by an ajax file, which will be executed very often. Right now each time the script is called I create a new connection (username, password, etc...) and at the end of the script I specifically close it (mysqli_close($con)).

Profile Performance On MySQL
I'm looking for recommendations on an app/function of mysql that will show a profile of the performance of my interaction with MySQL.

I'm a newb to php/MySQL, but not to programming/db. I'm about to launch a site and I'd like to look at a profile of the queries I'm running and if they are using indexes properly/doing full table scans, and other performance related items.

I've read up on the explain and benchmark functions, but they seem to require that the query is passed through them.

I'd prefer something more global like MS SQL's profiler that captures all of the actions, time expended and execution plan. Then I can look through it and see if any of the actions are taking longer than x time and then investigate further.

Does anything like this exist?

FYI, I'm on a shared hosted db server.

Performance And Multiple Databases
Are there performance issues when a SELECT query involves more than one database?

Is there any performance hit associated with specifying the database name in front of your table names? (Even if all tables come from the same DB, and if that db is the currently selected one)

Is it faster to change the current db and then perform a query with "locally-specified" table names?