Posted by Jerry Stuckle on 11/18/08 11:42

frizzle 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.
>
>
> 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.
>

MySQL 3.2.3 doesn't support subqueries.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]