[thelist] tuning MySQL

Edwin Martin edwin at bitstorm.org
Tue Nov 28 16:14:43 CST 2006


Matt McKeon wrote:
>>> After 2.5 hours with about 20k of queries 11k of them failed according 
>>> to phpMyAdmin.
>>>       
> Right sorry, forgot the details in my cloud of confusion.
> MySQL v.4.1.21
> PHP v.4.4.4
>
> The two tables I'm working with are both less then 1MB, the DB size is 
> only about 5MB. This PHP script/MySQL DB was working seemingly fine in 
> our previous setup..
>
> The SQL being used is:
> SELECT tbl_one.id, tbl_one.rating_current, tbl_one.rating_count, 
> tbl_two.name_internal
> FROM tbl_one, tbl_two
> WHERE tbl_one.id = tbl_two.id AND tbl_two.name_internal = $name
>
> UPDATE tbl_one
> SET rating_current = $rate, rating_count = $cnt
> WHERE id = $id
>   

These statements are quite simple and I don't think they can not be
optimized more.

How many simultaneous connection does the database get? Is it one script
with one connection of do you have hundreds of users using the database
simultaneous?

I can only think of three ways to optimize:

1) Add one or two indexes (on tbl_one.id and optionally tbl_two.id).
2) If you do a lot of updates in a row, you can "wrap" them in a single
update, speeding up the database a lot.
3) If you do have a lot of simultaneous connections, you might use a
database connection pool.

I don't have websites with more information, Google is your friend.

Edwin Martin

-- 
http://www.bitstorm.org/edwin/en/




More information about the thelist mailing list