Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

MySQL Optimization

Question about how to improve memory performance on MySQL DB



6:38 pm on Dec 4, 2009 (gmt 0)

5+ Year Member


I'm not a MySQL guru by any stretch, but I am trying to determine if there are some fairly straightforward ways to optimize DB calls and generally reduce the RAM requirements. Our server has been crashing fairly often of late (12+ times in a 24 hour period), in no small part due to the increase of holiday traffic. My sense is that the increased traffic is teasing out the more subtle issues with the code/queries being used. At any rate, hoping someone can point me to a 'primer' of sorts on this. I've perused the mysql.com site, which is great for specific, known questions- but it's not so great tutorial-wise. Any help appreciated- happy Friday!



9:00 pm on Dec 4, 2009 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

You might not like this . . . but the first place to look is your programming. This is usually where mysql begins to tank, in the way it's accessed.

I'll give you one example: one of the most common things I see in PHP is

execute a query to meet a where condition, getting the data
execute a SECOND query for mysql_num_rows

Loop through num rows, make repetitive selects for each record id to actually get at the data that was in hand already in the first query. I don't know where this logic comes from, but I see it a lot.

Another example is select * from when all you need is one or two fields.

I almost never see mysql_free_result() after queries, which frees up what? memory. :-)

You can add query benchmarks throughout your code, compare against command line queries, this will tighten up the programming and reveal possible areas of improvement.

Explain on your queries can tell you a lot too.

Inadequate error checking often leads one to think "it's working fine" when it's getting dangerously close to crashing your server.

For the DB itself, question a few items:

Are there any areas that I could be using integer lookups instead? Example, instead of

select * from products where category like '%some_cat%'

select * from category where category=5

where even though "category" is in a separate table, queries on integer fields will always run faster. Use this to your advantage. At every chance in programming, try to eliminate text searches, even if it means creating tables or complicated selects with joins. Save text searches for when you need them.

Are there any columns that have redundant data that doesn't need to be?


John owns 4 sites. Each site is id'ed by a unique ID. So we have to have row after row of redundant data . . . NOT!



Again, eliminating 4 rows in the users table, and allowing integers to rule the sites table, this is going to be light years faster, even with a join.

Are my columns wider than they need to be?

create table . . . zipcode varchar(255) <------

Are my columns indexed? Be careful with indexing, use it only on fields that will be searched in a query. Indexing all fields can sometimes have the opposite effect.

create table . . . zipcode varchar(15), index (zipcode(3))

On that topic, is my column the appropriate type? If your site commits to a five digit zip code, U.S. only (bad idea, but follow along) why would you want anything but this?

create table . . . zipcode int(5), index (zipcode(3))

Google for mysql normalization which will lead to many topics on tightening up the ship.


3:08 pm on Dec 7, 2009 (gmt 0)

WebmasterWorld Senior Member lammert is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

I almost never see mysql_free_result() after queries, which frees up what? memory. :-)

It frees up memory for large queries, but my experience is that when you have a large number of very small queries in your script it is better not to use this function because total execution time of the script is significantly increased with only slight reduction of memory usage. YMMV


1:31 am on Dec 11, 2009 (gmt 0)

5+ Year Member

Awesome, thanks for the detailed responses. I haven't actually written most of the DB code- it's locked away in PHPbb2 and Expression Engine. I would have assumed that these two products would be fairly well-written, at least in the manner described by roknbil, and they probably are. It will be interesting taking a look. Thanks again!

Featured Threads

Hot Threads This Week

Hot Threads This Month