|
|
Posted by Jerry Stuckle on 11/18/34 11:42
frizzle wrote:
> frizzle wrote:
>
>>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.
>
>
> Well, informed the host, and it appears i'm having mySQL upgraded this
> week! :)
> Do you expect things to speed up a lot (as far as you can say), or
> should i hold my horses?
>
> Frizzle.
>
It depends a lot on what they upgrade to. Hold your horses until you
see what happens.
Once you've got the new system up, run ANALYZE TABLE on all your tables
and try again. If it's still slow, see if they'll increase the sort
buffer sizes; the default is only 2M and you're probably returning a lot
more than that.
Another alternative if the problem is the sort buffer size is to just
get the basic information to select the records. Don't get the body and
URL, for instance, to cut down the size of the data being sorted. Then
as you fetch the rows, perform another MySQL query to get your text
data. If the problem is the sort buffer size, this might cut it down
enough to make the sort faster.
Plus - if you're not actually displaying all the returned data on one
page (what happens if you get 3000 rows returned?) you'll end up cutting
down additional overhead.
BTW - this really should be over in comp.databases.mysql, since it isn't
a PHP question.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|