[thelist] tuning MySQL

Pedro Arana pedro.arana.matus at gmail.com
Thu Nov 30 11:28:18 CST 2006


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

-- 
"La forma más rápida de lograr que un ingeniero solucione un problema
es declarar que el problema es insoluble."



More information about the thelist mailing list