|
|
Posted by frizzle on 03/13/06 16:08
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.
Navigation:
[Reply to this message]
|