[thelist] tuning MySQL

Matt McKeon matt at camadro.com
Thu Nov 30 11:42:50 CST 2006


Pedro Arana wrote:
> On 11/30/06, Matt McKeon <matt at camadro.com> wrote:
>   
>> Matt McKeon wrote:
>>     
>>> Pedro Arana wrote:
>>>       
>>>> On 11/28/06, Matt McKeon <matt at camadro.com> wrote:
>>>>         
>>>>> Hershel Robinson wrote:
>>>>> 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
>>>>> --
>>>>>           
>>>> hi!
>>>>
>>>> Wich information shows desc?
>>>>
>>>> DESC 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
>>>>
>>>>         
>> wow, sorry about that mess.. hopefully this looks better. All fields are
>> separated by a  -
>>
>> id - select_type - table - type - possible_keys - key - key_len - ref - rows - Extra
>>
>>
>> 1 - SIMPLE - tbl_one - ALL - PRIMARY - NULL - NULL - NULL - 270
>>
>> 1 - SIMPLE - tbl_two - eq_ref - PRIMARY - PRIMARY - 3 - db_daddy.tbl_one.id - 1 - Using where
>>
>>     
>
>
> this is real data? 270 rows for tbl_one they are not many
> anyway, your query dont use any index for search in tbl_one
> tbl_one.id is indexed?
> if no , can you define an index and test again?
>
> [] http://dev.mysql.com/doc/refman/4.1/en/create-index.html
>
>   
>> UPDATE tbl_one
>> SET rating_current = $rate, rating_count = $cnt
>> WHERE id = $id
>>     
>
> How much time take this? (before and after the index )
>
> greetings!
>
>    Pedro
>
>   
Yes the table is small and the queries are simple and both tables have 
the id field as the primary key. So I'm not sure why the query to 
tbl_one has to go through all rows.. They are still fast to execute here 
the results:
The select query takes 0.0001 sec.
The update query takes 0.0004 sec.

thanks,
 -matt



More information about the thelist mailing list