|
|
Posted by Jerry Stuckle on 03/14/06 13:51
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
==================
Navigation:
[Reply to this message]
|