建立新闻管理系统（全文） [英] Building a news management system (full story)
问题描述
嗨群星
我正在建立一个新闻网站，用户可以将新项目添加到mySQL
db。
它仍然在testfase中，但是它非常缓慢，我想想出我错误做什么，或者在它出现之前需要改变什么住...
我知道这是一个相当长的故事，但是如果有人能的话我会很高兴
帮助我在这里......
我已经阅读过关于优化数据库结构等的内容，但仍然无法加快速度。
（希望谷歌没有'太乱了几行...）
网站上还有4个相关的表格：
1. ne_topics
2. ne_fulltopic
3. ne_comments
4. gl_users
******** ****************************************** ******** ******************
表1：ne_topics
id（int 11）
author_id（int 11）//用户的wh的id om创建它。
创建（datetime）//创建项目时的日期时间
publication（datetime）//项目发布时的日期时间
title（varchar 35）//项目标题
url（varchar 35）//项目的url，任何az，0-9， -
和_
body（text）//项目正文，包括一些
BBcodes
visible（tinyint 1）//是项目是否可见（1或0）
粘性（tinyint 1）//粘性与否（1或0）
++目前99.977个testrows，puplication日期随机在1-1-2000
和31-12-2009,60,100 MB的数据，
id是PRIMARY
出版物，网址是独一无二的
可见有索引
*********************** *************************** *********************** ***
表2：ne_fulltopic
id（int 11）//匹配ne_topic.id，外键不
可用
fullbody（文本）//包含新闻的扩展版本
++目前1个testrow，2.904个字节数据，
id是PRIMARY
************************* ************************* ************************* *
表3：ne_comments
id（int 11）
topic_id（int 11）//到wich项目这样做评论属于
author_id（int 11）//用户创建它的id。
评论（文本）//用户的评论，包括一些
BBcodes
++目前有1个testrow，4.124个Bytesof数据，
id是PRIMARY
topic_id有索引
************************************* ************* ************************** *
表4：gl_users
id（int 11）
用户名（varchar 35）
电子邮件（varchar 125）
++ Curren tly 1 testrow，6.268字节的数据，
id是PRIMARY
用户名，电子邮件是独一无二的
***** ********************************************* ***** *********************
查询内容如下：
根本没有加入：0.59秒
$ get_news = mysql_query（"选择n .id`，n。发表`，n。标题`，
n .url`，n .body`，n .commentable`
FROM`ne_topics` n
WHERE n .visible` = 1
AND n .published`< = NOW（）
ORDER BY n .sticky` DESC，n。发布`DESC
LIMIT 12）或死（mysql_error（））;
仅限加入用户名：0.65秒
$ get_news = mysql_query（" SELECT n .id`，n。发表`，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。粘性`DESC，n。发布`DESC
LIMIT 12"）或死（mysql_error（））;
所需的所有JOIN：4.45秒
$ get_news = mysql_query（" SELECT n .id`，n。发表`，n。标题符号，
n .url`，n .body `，n .commentable`，
COUNT（f .id`）AS''fullbody''，
COUNT （c .id`）AS''no_of_comments''，
u .screenname`，u。电子邮件`
来自`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。发布`DESC
LIMIT 12"
COUNT（f .id`）AS''fullbody''是确定是否应该出现阅读更多
链接。
为什么哦为什么这么慢？我真的希望有人可以提供帮助，因为我现在已经花了好几个星期的时间尝试了几个星期但是无法理解...... :(
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.
推荐答案
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）或死（mysql_error（））;
仅加入用户名：0.65秒
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。电子邮件
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）或者死（mysql_error（））;
所需的所有JOIN：4.45秒
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（"选择n .id`，n。发表`，n。标题`，
n .url`，n .body`，n .commentable`，
COUNT（f .id`）AS''fullbody''，
COUNT（c .id`）AS''no_of_comments''，
u .`screenname`，你.email`
来自`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。发布`< =现在（）
GROUP BY n .id`，u .screenname`
ORDER BY n。 `sticky` DESC，n。发表`DESC
LIMIT 12"
COUNT（f .id`）AS''fullbody''是确定是否有一个阅读更多
链接应该出现。
为什么哦为什么这么慢？我真的希望有人可以提供帮助，因为我现在已经花了好几个星期的时间尝试了几个星期但是无法理解...... :(
Frizzle。
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.
这篇关于建立新闻管理系统（全文）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
