[thelist] tuning MySQL

[Matt McKeon]

Hershel Robinson wrote:
>> After 2.5 hours with about 20k of queries 11k of them failed according 
>> to phpMyAdmin.
>> So I'm asking what can I do to optimize this? I have primary keys and 
>> the scripts that are connecting to it are only doing 1-2 SELECT's and 1 
>> UPDATE
>> I increased max_connections, and query_cache_size but it just isn't 
>> doing it. I'm just not sure where to begin to look for bottle necks. Any 
>> suggestions?
>>     
>
> Without any details as to your MySQL version, database size and 
> structure and (in particular) the nature of the SQL being executed, it 
> is virtually impossible to suggest ways to improve.
>
> The SQL statements being executed are most likely an area which can be 
> improved, from what it sounds. A SELECT can include lots of elements, as 
> in no JOINs or 15 LEFT JOINs. 15 LEFT JOINs happening twice a second 
> over a period of 2.5 hours *could* be the issue.
>
> Another question to ask is do you have all foreign relationships 
> (CONSTRAINTS) properly defined?
>
> Perhaps you can send a sample SELECT statement?
>
> Hershel
>
>   
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

Hope that makes it a little easier, o and its in a RHE 4 environment.

-matt