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 ....
6  2138 
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 ....
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 ....
"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 !!
"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 !!
"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 :-)
"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 :-) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Yang Li Ke |
last post by:
Hi guys,
Is it possible to know the internet speed of the visitors with php?
Thanx
--
Yang
|
by: Rob Ristroph |
last post by:
I have tried out PHP 5 for the first time (with assistance from this
group -- thanks!).
The people I was working with have a site that uses lots of php
objects. They are having problems with...
|
by: Jacek Generowicz |
last post by:
I have a program in which I make very good use of a memoizer:
def memoize(callable):
cache = {}
def proxy(*args):
try: return cache
except KeyError: return cache.setdefault(args,...
|
by: floortje |
last post by:
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...
|
by: YAZ |
last post by:
Hello,
I have a dll which do some number crunching. Performances (execution
speed) are very important in my application. I use VC6 to compile the
DLL.
A friend of mine told me that in Visual...
|
by: Ham |
last post by:
Yeah,
Gotto work with my VB.Net graphic application for days, do any possible type
of code optimization, check for unhandled errors and finally come up with
sth that can't process 2D graphics and...
|
by: Jassim Rahma |
last post by:
I want to detect the internet speed using C# to show the user on what speed
he's connecting to internet?
|
by: kyosohma |
last post by:
Hi,
We use a script here at work that runs whenever someone logs into
their machine that logs various bits of information to a database. One
of those bits is the CPU's model and speed. While...
|
by: nestle |
last post by:
I have DSL with a download speed of 32MB/s and an upload speed of 8MB/s(according to my ISP), and I am using a router. My upload speed is always between 8MB/s and 9MB/s(which is above the max upload...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
