Posted by frizzle on 03/14/06 16:35

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

[Back to original message]