Posted by frizzle on 11/18/22 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.

Step by step it seems i'm getting closer to my goal!!!
I now have another appraoch, which takes the time from 5 secs to .5
(still quite slow, but acceptable) I first select the id's from the
right news articles,
and then, i perform the joins etc. on only those returned articles
using subqueries.

But, (of course there's a but) i get an error. I've read upon
subqueries, using 'IN' in subqueries, but it still doesn't make sense.
Separately both queries work, but together they don't.

The query is below, and the error looks like this:
You have an error in your SQL syntax near 'SELECT t.`id` FROM
`ne_topics` t WHERE t.`' at line 12

SELECT n.`id`, n.`published`, n.`title`, n.`url`, n.`body`,
COUNT(f.`id`) AS 'fullbody',
COUNT(c.`id`) AS 'no_of_comments',
u.`screenname`, u.`email`
FROM `ne_topics` n
LEFT JOIN `ne_fulltopic` f
ON (f.`id` = n.`id`)
LEFT JOIN `ne_comments` c
ON (c.`topic_id` = n.`id`)
LEFT JOIN `gl_users` u
ON (u.`id` = n.`author_id`)
WHERE n.`id` IN( SELECT `id`
FROM `ne_topics`
WHERE `visible` = 1
AND `published` <= NOW()
ORDER BY `sticky` DESC, `published` DESC
LIMIT 12 )
GROUP BY n.`id`"

Why does this give me an error?

Frizzle.

[Back to original message]