MySQL performance


I'm running a copy of MySQL database on our web server - this is not a dedicated MySQL server, but the one web application that pulls data from this db has really poor performance.

I inherited this whole mess from another developer. I can understand large, complex connections on a shared server could be problematic, but I'm talking like 15 seconds to load 700 records.

I know next to nothing about this database, but was wondering what are the general things I should look at to "compact-repair" the db, recommended cache settings, etc.

Can anyone suggest what might be going on here? Or some remedies?

This is on a Windows Server 2003 box with all the latest updates. MySQL database has approximately 20 tables, most of them holding only a few columns and a few records.

I've also confirmed on the old website that performance was slow, and now our new code for their new website is sluggish too, so I do think it's the db.


Bill HendersonWeb MarketingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ioannis AnifantakisProgramming InstructorCommented:
From what I understand, there is some big mess, such you don't even know where to exactly start so that we can point some direction.

In my honest oppinion, the very first thing that comes to someone's mind when we talk about performance is the use of indeces.

>I know next to nothing about this database, but was wondering what are the general things I should look at to "compact-repair" the db, recommended cache settings, etc.

If you don't know much about database design, then I urge you to start looking on the topic "index".

When you search for the entry "john" in a database without the use of indeces, each record will be processed independently until you get to the entry you want.  Also recursive searches will go from record 1 to last record again and again... Think of it... it can be SLOW.

If you use "INDEX" in your database, then a "dictionary" is created.  Assume you look for the word "john" in a search of yours.  This dictionary will take you directly to the letter "j".. skiiping all letters from "a" to "i".  Then since its at "j" will start looking for the remainder "o" (as  in john)... and so on.

It can end in 1 step instead of 100000000 steps.

When you use indeces, your seach increases dramatically, but be careful as to use only the necessary indexes, because the more you use, the faster is the search, but the slower the "insert, alter, delete" as each such action has to also change some of the contents of each index.

Primary keys and foreign keys are by default indexes.


Good tips recarding performance you can also find here

       1.  Use EXPLAIN to profile the query execution plan
       2. Use Slow Query Log (always have it on!) *
       3. Don’t use DISTINCT when you have or could use GROUP BY
       4. Insert performance
             1. Batch INSERT and REPLACE
             2. Use LOAD DATA instead of INSERT
       5. LIMIT m,n may not be as fast as it sounds
       6. Don’t use ORDER BY RAND() if you have > ~2K records
       7. Use SQL_NO_CACHE when you are SELECTing frequently updated data or large sets of data
       8. Avoid wildcards at the start of LIKE queries
       9. Avoid correlated subqueries and in select and where clause (try to avoid in)
      10. ORDER BY and LIMIT work best with equalities and covered indexes
      11. Separate text/blobs from metadata, don’t put text/blobs in results if you don’t need them
      12. Delete small amounts at a time if you can
      13. Make similar queries consistent so cache is used
      14. Don’t use deprecated features
      15. Turning OR on multiple index fields (<5.0) into UNION may speed things up (with LIMIT), after 5.0 the index_merge should pick stuff up.
      16. Use INSERT … ON DUPLICATE KEY update (INSERT IGNORE) to avoid having to SELECT


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
1.  Check indexes on each table (compare how you do selects, joins)
2.  check out the innodb_buffer_pool_size, which is a variable you set at start up (
Ioannis AnifantakisProgramming InstructorCommented:
I almost forgot, one thing you should do first before anything else, is check-optimize-repair your tables.
go to the directory of mysql\bin and type the following:
mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

Open in new window

Ioannis AnifantakisProgramming InstructorCommented:
Finally, a very good article written by a member here in Experts-Exchange will help you alot.
Bill HendersonWeb MarketingAuthor Commented:
Sorry for the delay! Thanks for the suggestions...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.