|
|
Posted by frizzle on 11/18/69 11:42
Jerry Stuckle wrote:
> 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
> ==================
- 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.
is this the same as what i do when i first only get the concerning
id's, and then
get all info concerning those id's ?
- 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.
Results in this case are limited to 12 rows. I heard that "SELECT
fieldname"
and "LIMIT" both drop unbothered data *after* performing the 'search'.
Does this
mean that only the result is put into the buffer, or all data, and only
the requested
data is returned from the buffer?
Anyways, thanks for the effort and pointing out so much.
Frizzle.
(PS: You're saying the query should be fine of mySQL 4.1 ? )
Navigation:
[Reply to this message]
|