Building a news management system (full story)

[frizzle]

Hi groupies

I'm building a news site, to wich a user can add new items into a mySQL
db.
It's still in testfase, but it's so extremely slow, i want to figure
out what i'm doing wrong, or what to change before it goes live ...
I know it's quite a long story, but i would be so happy if anyone could
help me out here ...
I've read on optimizing DB structure etc, but still cannot speed things
up ...

(Hope Google doesn't mess up the lines too much ... )

There are 4 relevant tables (yet) in the site:
1. ne_topics
2. ne_fulltopic
3. ne_comments
4. gl_users

************************************************** **************************

Table 1: ne_topics
id (int 11)
author_id (int 11) // user's id of whom created it.
creation (datetime) // datetime when item was created
publication (datetime) // datetime when item is (to be) published
title (varchar 35) // title of the item
url (varchar 35) // url of the item, anything a-z, 0-9, -
and _
body (text) // body text of item, including some
BBcodes
visible (tinyint 1) // is item visible or not ( 1 or 0 )
sticky (tinyint 1) // sticky or not ( 1 or 0 )

++ Currently 99.977 testrows, puplication dates random between 1-1-2000
and 31-12-2009, 60,1 MB of data,
id is PRIMARY
publication, url are UNIQUE
visible has INDEX

************************************************** **************************

Table 2: ne_fulltopic
id (int 11) // matches ne_topic.id, foreign keys not
available
fullbody (text) // contains an extended version of news

++ Currently 1 testrow, 2.904 Bytes of data,
id is PRIMARY

************************************************** **************************

Table 3: ne_comments
id (int 11)
topic_id (int 11) // to wich item does this comment belong
author_id (int 11) // user's id of whom created it.
comment (text) // comment of the user, including some
BBcodes

++ Currently 1 testrow, 4.124 Bytesof data,
id is PRIMARY
topic_id has INDEX

************************************************** **************************

Table 4: gl_users
id (int 11)
username (varchar 35)
email (varchar 125)

++ Currently 1 testrow, 6.268 Bytes of data,
id is PRIMARY
username, email are UNIQUE

************************************************** **************************

The queries are as follows:

no joins at all: 0.59 secs
$get_news = mysql_query(" SELECT n.`id`, n.`published`, n.`title`,
n.`url`, n.`body`, n.`commentable`
FROM `ne_topics` n
WHERE n.`visible` = 1
AND n.`published` <= NOW()
ORDER BY n.`sticky` DESC, n.`published` DESC
LIMIT 12")or die(mysql_error());

JOIN only username: 0.65 secs
$get_news = mysql_query(" SELECT n.`id`, n.`published`, n.`title`,
n.`url`, n.`body`, n.`commentable`,
u.`screenname`, u.`email`
FROM `ne_topics` n, `gl_users` u
WHERE n.`visible` = 1
AND n.`published` <= NOW()
ORDER BY n.`sticky` DESC, n.`published` DESC
LIMIT 12")or die(mysql_error());

all JOINs needed: 4.45 secs
$get_news = mysql_query(" SELECT n.`id`, n.`published`, n.`title`,
n.`url`, n.`body`, n.`commentable`,
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.`visible` = 1
AND n.`published` <= NOW()
GROUP BY n.`id`, u.`screenname`
ORDER BY n.`sticky` DESC, n.`published` DESC
LIMIT 12"

COUNT(f.`id`) AS 'fullbody' is to determine wheter or not a "read more"
link should appear.

Why oh why is this sooo slow? I really hope someone can help, since i'm
trying things for weeks now but just cannot figure it out ... :(

Frizzle.

[Jerry Stuckle]

frizzle wrote:

Hi groupies

I'm building a news site, to wich a user can add new items into a mySQL
db.
It's still in testfase, but it's so extremely slow, i want to figure
out what i'm doing wrong, or what to change before it goes live ...
I know it's quite a long story, but i would be so happy if anyone could
help me out here ...
I've read on optimizing DB structure etc, but still cannot speed things
up ...

(Hope Google doesn't mess up the lines too much ... )

There are 4 relevant tables (yet) in the site:
1. ne_topics
2. ne_fulltopic
3. ne_comments
4. gl_users

************************************************** **************************

Table 1: ne_topics
id (int 11)
author_id (int 11) // user's id of whom created it.
creation (datetime) // datetime when item was created
publication (datetime) // datetime when item is (to be) published
title (varchar 35) // title of the item
url (varchar 35) // url of the item, anything a-z, 0-9, -
and _
body (text) // body text of item, including some
BBcodes
visible (tinyint 1) // is item visible or not ( 1 or 0 )
sticky (tinyint 1) // sticky or not ( 1 or 0 )

++ Currently 99.977 testrows, puplication dates random between 1-1-2000
and 31-12-2009, 60,1 MB of data,
id is PRIMARY
publication, url are UNIQUE
visible has INDEX

************************************************** **************************

Table 2: ne_fulltopic
id (int 11) // matches ne_topic.id, foreign keys not
available
fullbody (text) // contains an extended version of news

++ Currently 1 testrow, 2.904 Bytes of data,
id is PRIMARY

************************************************** **************************

Table 3: ne_comments
id (int 11)
topic_id (int 11) // to wich item does this comment belong
author_id (int 11) // user's id of whom created it.
comment (text) // comment of the user, including some
BBcodes

++ Currently 1 testrow, 4.124 Bytesof data,
id is PRIMARY
topic_id has INDEX

************************************************** **************************

Table 4: gl_users
id (int 11)
username (varchar 35)
email (varchar 125)

++ Currently 1 testrow, 6.268 Bytes of data,
id is PRIMARY
username, email are UNIQUE

************************************************** **************************

The queries are as follows:

no joins at all: 0.59 secs
$get_news = mysql_query(" SELECT n.`id`, n.`published`, n.`title`,
n.`url`, n.`body`, n.`commentable`
FROM `ne_topics` n
WHERE n.`visible` = 1
AND n.`published` <= NOW()
ORDER BY n.`sticky` DESC, n.`published` DESC
LIMIT 12")or die(mysql_error());

JOIN only username: 0.65 secs
$get_news = mysql_query(" SELECT n.`id`, n.`published`, n.`title`,
n.`url`, n.`body`, n.`commentable`,
u.`screenname`, u.`email`
FROM `ne_topics` n, `gl_users` u
WHERE n.`visible` = 1
AND n.`published` <= NOW()
ORDER BY n.`sticky` DESC, n.`published` DESC
LIMIT 12")or die(mysql_error());

all JOINs needed: 4.45 secs
$get_news = mysql_query(" SELECT n.`id`, n.`published`, n.`title`,
n.`url`, n.`body`, n.`commentable`,
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.`visible` = 1
AND n.`published` <= NOW()
GROUP BY n.`id`, u.`screenname`
ORDER BY n.`sticky` DESC, n.`published` DESC
LIMIT 12"

COUNT(f.`id`) AS 'fullbody' is to determine wheter or not a "read more"
link should appear.

Why oh why is this sooo slow? I really hope someone can help, since i'm
trying things for weeks now but just cannot figure it out ... :(

Frizzle.

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.
js*******@attglobal.net
==================

[frizzle]

Jerry Stuckle wrote:

frizzle wrote:

Hi groupies

I'm building a news site, to wich a user can add new items into a mySQL
db.
It's still in testfase, but it's so extremely slow, i want to figure
out what i'm doing wrong, or what to change before it goes live ...
I know it's quite a long story, but i would be so happy if anyone could
help me out here ...
I've read on optimizing DB structure etc, but still cannot speed things
up ...

(Hope Google doesn't mess up the lines too much ... )

There are 4 relevant tables (yet) in the site:
1. ne_topics
2. ne_fulltopic
3. ne_comments
4. gl_users

************************************************** **************************

Table 1: ne_topics
id (int 11)
author_id (int 11) // user's id of whom created it.
creation (datetime) // datetime when item was created
publication (datetime) // datetime when item is (to be) published
title (varchar 35) // title of the item
url (varchar 35) // url of the item, anything a-z, 0-9, -
and _
body (text) // body text of item, including some
BBcodes
visible (tinyint 1) // is item visible or not ( 1 or 0 )
sticky (tinyint 1) // sticky or not ( 1 or 0 )

++ Currently 99.977 testrows, puplication dates random between 1-1-2000
and 31-12-2009, 60,1 MB of data,
id is PRIMARY
publication, url are UNIQUE
visible has INDEX

************************************************** **************************

Table 2: ne_fulltopic
id (int 11) // matches ne_topic.id, foreign keys not
available
fullbody (text) // contains an extended version of news

++ Currently 1 testrow, 2.904 Bytes of data,
id is PRIMARY

************************************************** **************************

Table 3: ne_comments
id (int 11)
topic_id (int 11) // to wich item does this comment belong
author_id (int 11) // user's id of whom created it.
comment (text) // comment of the user, including some
BBcodes

++ Currently 1 testrow, 4.124 Bytesof data,
id is PRIMARY
topic_id has INDEX

************************************************** **************************

Table 4: gl_users
id (int 11)
username (varchar 35)
email (varchar 125)

++ Currently 1 testrow, 6.268 Bytes of data,
id is PRIMARY
username, email are UNIQUE

************************************************** **************************

The queries are as follows:

no joins at all: 0.59 secs
$get_news = mysql_query(" SELECT n.`id`, n.`published`, n.`title`,
n.`url`, n.`body`, n.`commentable`
FROM `ne_topics` n
WHERE n.`visible` = 1
AND n.`published` <= NOW()
ORDER BY n.`sticky` DESC, n.`published` DESC
LIMIT 12")or die(mysql_error());

JOIN only username: 0.65 secs
$get_news = mysql_query(" SELECT n.`id`, n.`published`, n.`title`,
n.`url`, n.`body`, n.`commentable`,
u.`screenname`, u.`email`
FROM `ne_topics` n, `gl_users` u
WHERE n.`visible` = 1
AND n.`published` <= NOW()
ORDER BY n.`sticky` DESC, n.`published` DESC
LIMIT 12")or die(mysql_error());

all JOINs needed: 4.45 secs
$get_news = mysql_query(" SELECT n.`id`, n.`published`, n.`title`,
n.`url`, n.`body`, n.`commentable`,
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.`visible` = 1
AND n.`published` <= NOW()
GROUP BY n.`id`, u.`screenname`
ORDER BY n.`sticky` DESC, n.`published` DESC
LIMIT 12"

COUNT(f.`id`) AS 'fullbody' is to determine wheter or not a "read more"
link should appear.

Why oh why is this sooo slow? I really hope someone can help, since i'm
trying things for weeks now but just cannot figure it out ... :(

Frizzle.

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.
js*******@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.

[Jerry Stuckle]

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.
js*******@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.
js*******@attglobal.net
==================

[frizzle]

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.
js*******@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.
js*******@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.

[frizzle]

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.
>js*******@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.
js*******@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.

[frizzle]

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.
>>js*******@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.
js*******@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.

[Oli Filth]

frizzle said the following on 15/03/2006 16:28:

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`"

Are you sure that's the error that corresponds to this query? Because
you don't have anything called "t" in your query, and nothing in your
query matches the syntax referred to in the error message...

BTW, it's not necessary to back-tick (`) every field name in the query,
and it makes it quite hard to read. Also, your aliases ("fullbody" and
"no_of_comments") are quoted in single-quotes, which I is wrong, I think.
--
Oli

[frizzle]

Oli Filth wrote:

frizzle said the following on 15/03/2006 16:28:

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`"

Are you sure that's the error that corresponds to this query? Because
you don't have anything called "t" in your query, and nothing in your
query matches the syntax referred to in the error message...

BTW, it's not necessary to back-tick (`) every field name in the query,
and it makes it quite hard to read. Also, your aliases ("fullbody" and
"no_of_comments") are quoted in single-quotes, which I is wrong, I think.
--
Oli

Sorry, i copy posted the wrong error. :$ It should be:
You have an error in your SQL syntax near 'SELECT `id` FROM `ne_topics`
WHERE `visibl' at line 12

[Jerry Stuckle]

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.
>js*******@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.
js*******@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.
js*******@attglobal.net
==================

[Jerry Stuckle]

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.
>>js*******@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.
js*******@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.
js*******@attglobal.net
==================

[frizzle]

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.
>>js*******@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.
js*******@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.
js*******@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 ? )