Posted by frizzle on 11/17/48 11:42

Jerry Stuckle wrote:
> frizzle wrote:
> > Jerry Stuckle wrote:
> >
> >>
> >>First of all, did you run EXPLAIN on your queries?
> >>
> >>Also, is it even a MySQL problem? How's your hardware, for instance?
> >>And what else is using system resources?
> >>
> >>A lot of things can affect performance. Before you can improve the
> >>performance you have to identify why it's running slow.
> >>
> >>
> >>--
> >>==================
> >>Remove the "x" from my email address
> >>Jerry Stuckle
> >>JDS Computer Training Corp.
> >>jstucklex@attglobal.net
> >>==================
> >
> >
> > Not to blow you off, but when i try to optimize things, i can go as far
> > as they're in my hands. (In this case only the PHP & MySQL)
> >
> > EXPLAIN of the last query gave me the following:
> > table type possible_keys key key_len ref rows
> > Extra
> > n range published,visible published 8 NULL 99977
> > where used; Using temporary; Using filesort
> > f index PRIMARY PRIMARY 3 NULL 1
> > Using index
> > c ALL topic_id NULL NULL NULL 1
> > u ALL PRIMARY,id NULL NULL NULL 1
> >
> >
> >
> > MySQL Version: MySQL 3.23.58
> > PHP Version 4.3.10 (if this matters)
> >
> >
> > I'm not sure what else i could tell you.
> >
> > Frizzle.
> >
>
> OK, well you didn't say you had run EXPLAIN on the queries, so I had no
> way of knowing. A lot of people don't understand it.
>
> A couple of things. First of all, it's not using an index on your
> ne_comments and gl_users tables. I guess that would be normal since you
> only have 1 row in each.
>
> The other thing is it's doing a filesort on the temporary table. I
> don't know if adding indicies to the sticky and published/visible (not
> sure which one it is - your table shows `visible` but your query shows
> `published` will help.
>
> You could also check your sort buffer sizes. The defaults may be too
> small for the amount of data you're sorting.
>
> And you could run ANALYZE TABLE on your tables, but I don't think from
> your EXPLAIN output it will help much if at all.
>
> Additionally, I've found sometimes INNODB tables can improve performance.
>
> And if you can upgrade to a more recent version of MySQL (3.2.23 is very
> old), you'll probably get better performance. Optimization has been
> improved since your version came out.
>
> Hope this helps a little.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================

Hmm, so no real solution. Too bad ... :( I was really hoping to have
this problem solved.
Published has an index to it already, and InoDB isn't available on my
server ... :(

Thanks anyway.
I'll have to find another way for this system i guess, this is way too
slow. Do you maybe have any other structural tips or so to achieve the
same(ish) goal ?

Frizzle.

[Back to original message]