468,290 Members | 1,998 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,290 developers. It's quick & easy.

Can I speed this up alittle ?

Hi, I got a forum on my website .... it;s located in 2 databases: 1 for the
topic names and one for the text and user info: the first has 20371 entries
... the second 2124

CREATE TABLE `forumtext` (
`id` int(5) NOT NULL auto_increment,
`text` text NOT NULL,
`naam` varchar(100) NOT NULL default '',
`email` varchar(100) NOT NULL default '',
`datum` timestamp(14) NOT NULL,
`wilmail` char(1) NOT NULL default '',
`id_forumtops` int(5) NOT NULL default '0',
`id_users` varchar(5) NOT NULL default '',
`id_gast` varchar(100) NOT NULL default '',
`ip` varchar(20) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=20371 ;
# --------------------------------------------------------
CREATE TABLE `forumtops` (
`id` int(5) NOT NULL auto_increment,
`forumnaam` varchar(200) NOT NULL default '',
`sticky` varchar(12) NOT NULL default '',
`date` timestamp(14) NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2124 ;
I use these queries:
to select all topics ordered by last post
SELECT forumtops.*, count(forumtext.id_forumtops) as teller,
MAX(forumtext.datum) as laatste
FROM forumtext, forumtops
WHERE forumtext.id_forumtops=forumtops.id
GROUP BY forumtops.id
ORDER BY forumtops.sticky DESC, laatste DESC")

and to select one specific topic
ELECT * FROM forumtext,forumtops
WHERE forumtext.id_forumtops=\"$_GET[vraag]\"
AND forumtops.id =\"$_GET[vraag]\"
ORDER BY forumtext.datum");
Now here is my question ... can I do anything to speed this up cayuse it;s
driving me mad ....
Jul 19 '05 #1
6 1935
Yes! (You can speed it up a lot!)

Add several indexes:
On table forumtext add indexes for fields datum and id_forumtops
On table forumtops add an index for field sticky

I would suspect that other queires will also be improved by adding other
indexes

Have a look in the manual at the EXPLAIN syntax and how to optimise queries
based on the reports in generates.

"floortje" <fl******@floortje.floortje> wrote in message
news:3f*********************@dreader9.news.xs4all. nl...
Hi, I got a forum on my website .... it;s located in 2 databases: 1 for the topic names and one for the text and user info: the first has 20371 entries .. the second 2124

CREATE TABLE `forumtext` (
`id` int(5) NOT NULL auto_increment,
`text` text NOT NULL,
`naam` varchar(100) NOT NULL default '',
`email` varchar(100) NOT NULL default '',
`datum` timestamp(14) NOT NULL,
`wilmail` char(1) NOT NULL default '',
`id_forumtops` int(5) NOT NULL default '0',
`id_users` varchar(5) NOT NULL default '',
`id_gast` varchar(100) NOT NULL default '',
`ip` varchar(20) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=20371 ;
# --------------------------------------------------------
CREATE TABLE `forumtops` (
`id` int(5) NOT NULL auto_increment,
`forumnaam` varchar(200) NOT NULL default '',
`sticky` varchar(12) NOT NULL default '',
`date` timestamp(14) NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2124 ;
I use these queries:
to select all topics ordered by last post
SELECT forumtops.*, count(forumtext.id_forumtops) as teller,
MAX(forumtext.datum) as laatste
FROM forumtext, forumtops
WHERE forumtext.id_forumtops=forumtops.id
GROUP BY forumtops.id
ORDER BY forumtops.sticky DESC, laatste DESC")

and to select one specific topic
ELECT * FROM forumtext,forumtops
WHERE forumtext.id_forumtops=\"$_GET[vraag]\"
AND forumtops.id =\"$_GET[vraag]\"
ORDER BY forumtext.datum");
Now here is my question ... can I do anything to speed this up cayuse it;s
driving me mad ....

Jul 19 '05 #2
Yes! (You can speed it up a lot!)

Add several indexes:
On table forumtext add indexes for fields datum and id_forumtops
On table forumtops add an index for field sticky

I would suspect that other queires will also be improved by adding other
indexes

Have a look in the manual at the EXPLAIN syntax and how to optimise queries
based on the reports in generates.

"floortje" <fl******@floortje.floortje> wrote in message
news:3f*********************@dreader9.news.xs4all. nl...
Hi, I got a forum on my website .... it;s located in 2 databases: 1 for the topic names and one for the text and user info: the first has 20371 entries .. the second 2124

CREATE TABLE `forumtext` (
`id` int(5) NOT NULL auto_increment,
`text` text NOT NULL,
`naam` varchar(100) NOT NULL default '',
`email` varchar(100) NOT NULL default '',
`datum` timestamp(14) NOT NULL,
`wilmail` char(1) NOT NULL default '',
`id_forumtops` int(5) NOT NULL default '0',
`id_users` varchar(5) NOT NULL default '',
`id_gast` varchar(100) NOT NULL default '',
`ip` varchar(20) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=20371 ;
# --------------------------------------------------------
CREATE TABLE `forumtops` (
`id` int(5) NOT NULL auto_increment,
`forumnaam` varchar(200) NOT NULL default '',
`sticky` varchar(12) NOT NULL default '',
`date` timestamp(14) NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2124 ;
I use these queries:
to select all topics ordered by last post
SELECT forumtops.*, count(forumtext.id_forumtops) as teller,
MAX(forumtext.datum) as laatste
FROM forumtext, forumtops
WHERE forumtext.id_forumtops=forumtops.id
GROUP BY forumtops.id
ORDER BY forumtops.sticky DESC, laatste DESC")

and to select one specific topic
ELECT * FROM forumtext,forumtops
WHERE forumtext.id_forumtops=\"$_GET[vraag]\"
AND forumtops.id =\"$_GET[vraag]\"
ORDER BY forumtext.datum");
Now here is my question ... can I do anything to speed this up cayuse it;s
driving me mad ....

Jul 19 '05 #3

"2metre" <nw*@hersham.net> schreef in bericht
news:bf**********@titan.btinternet.com...
Yes! (You can speed it up a lot!)

Add several indexes:
On table forumtext add indexes for fields datum and id_forumtops
On table forumtops add an index for field sticky

I would suspect that other queires will also be improved by adding other
indexes

Have a look in the manual at the EXPLAIN syntax and how to optimise queries based on the reports in generates.

I will do that ... thx u !!
Jul 19 '05 #4

"2metre" <nw*@hersham.net> schreef in bericht
news:bf**********@titan.btinternet.com...
Yes! (You can speed it up a lot!)

Add several indexes:
On table forumtext add indexes for fields datum and id_forumtops
On table forumtops add an index for field sticky

I would suspect that other queires will also be improved by adding other
indexes

Have a look in the manual at the EXPLAIN syntax and how to optimise queries based on the reports in generates.

I will do that ... thx u !!
Jul 19 '05 #5

"Peter" <ku**@pox.nl> schreef in bericht
news:81*************************@posting.google.co m...
Heb je wel indexen gemaakt?

nope .. wist ik veel datter binnen no time 20k replies zouden zijn ... had
gedacht aan 2 per dag ofzow :-)
Jul 19 '05 #6

"Peter" <ku**@pox.nl> schreef in bericht
news:81*************************@posting.google.co m...
Heb je wel indexen gemaakt?

nope .. wist ik veel datter binnen no time 20k replies zouden zijn ... had
gedacht aan 2 per dag ofzow :-)
Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by Yang Li Ke | last post: by
8 posts views Thread by Rob Ristroph | last post: by
34 posts views Thread by Jacek Generowicz | last post: by
reply views Thread by floortje | last post: by
7 posts views Thread by YAZ | last post: by
6 posts views Thread by Ham | last post: by
6 posts views Thread by Jassim Rahma | last post: by
11 posts views Thread by kyosohma | last post: by
2 posts views Thread by MrBee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.