Hi again,
Just did another test which I find very interesting....
Using the Paradox table instead of the MySQL table I managed to load each
and every record & search through every field (including all the text in all
the blob field) in just 6 seconds. and still doing a simple group SELECT
GROUP BY on the same table in MySQL takes 13 seconds... still can't belive
it.
--
Thomas
----- Original Message -----
From: "Bhavin Vyas" <bvyas3@stripped>
To: "Thomas Kvamme" <thokvamm@stripped>; <mysql@stripped>
Sent: Saturday, January 25, 2003 5:15 PM
Subject: Re: How to speed things up in MySQL ?
> Sounds weird. Can you e-mail us a 'mysqldump' of the table defination with
> the data?
>
> Thanks,
> Bhavin.
> ----- Original Message -----
> From: "Thomas Kvamme" <thokvamm@stripped>
> To: <mysql@stripped>
> Sent: Saturday, January 25, 2003 7:36 AM
> Subject: How to speed things up in MySQL ?
>
>
> > Hello,
> >
> > While reading this you may think this is off-topic... but please read
on..
> > I'll get on-topic in the end :-))
> >
> > First of all I have Web Server on which I have a Paradox table with 320
> > records.
> >
> > I also have a CGI Script (or program if you like) I made in Borland
Delphi
> > which I use to access the Table...
> >
> > When I enter the address of my CGI-script in my Internet Browser the
> > following happens:
> >
> > The CGI script on the Web Server is starting.
> > The CGI script reads the SQL command to execute passed on to the
script
> > through the HTTP protocol.
> > The CGI script establish connection with the Borland Database Engine
> > (Loading BDE dll's etc..)
> > The CGI script opens/initialize the Paradox Table.
> > The CGI script execute the SQL command sent to it.
> > The CGI script sends back the result of the SQL command to the
Browser
> > (HTTP protocol).
> > The CGI script closes down (shutting down db connection etc..).
> >
> > My PC and my Web Server is connected on the same LAN (switched 100
MBit/s)
> >
> > All of the above is completed in 1.5 seconds.
> >
> > ** here the fun begins **
> >
> > I have now successfully installed MySQL (on the same WebServer) and
> > converted the Paradox Table to MySQL Table type: MyISAM.
> >
> > Database Name: TDD
> > Table Name: DVD
> >
> > This new Table contains the same 320 records using the same field
> definition
> > as in the Paradox Table.
> >
> > On my Web Server I go into \MySQL\bin\ and start "mysql -uroot -p" and
> > execute the same SQL command as sent to the Paradox table through the
CGI
> > script. see screen dump below for result.
> >
> > ******** SCREEN DUMP **********
> > F:\MySQL\bin>mysql -uroot -p
> > Enter password: ******
> > Welcome to the MySQL monitor. Commands end with ; or \g.
> > Your MySQL connection id is 3726 to server version: 4.0.9-gamma-nt
> >
> > Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> >
> > mysql> use tdd
> > Database changed
> > mysql> select count(id) as Films, ProductionYear from DVD
> > -> group by ProductionYear
> > -> order by ProductionYear desc;
> > +-------+----------------+
> > | Films | ProductionYear |
> > +-------+----------------+
> > | 19 | 2002 |
> > | 44 | 2001 |
> > | 58 | 2000 |
> > | 41 | 1999 |
> > | 27 | 1998 |
> > | 16 | 1997 |
> > | 11 | 1996 |
> > | 9 | 1995 |
> > | 12 | 1994 |
> > | 8 | 1993 |
> > | 7 | 1992 |
> > | 3 | 1991 |
> > | 7 | 1990 |
> > | 6 | 1989 |
> > | 2 | 1988 |
> > | 6 | 1987 |
> > | 2 | 1986 |
> > | 2 | 1985 |
> > | 6 | 1984 |
> > | 4 | 1983 |
> > | 3 | 1982 |
> > | 3 | 1981 |
> > | 2 | 1980 |
> > | 3 | 1979 |
> > | 2 | 1978 |
> > | 1 | 1976 |
> > | 1 | 1975 |
> > | 2 | 1974 |
> > | 2 | 1973 |
> > | 1 | 1972 |
> > | 1 | 1971 |
> > | 1 | 1970 |
> > | 1 | 1969 |
> > | 1 | 1968 |
> > | 2 | 1965 |
> > | 1 | 1964 |
> > | 1 | 1959 |
> > | 1 | 1940 |
> > | 1 | 1937 |
> > +-------+----------------+
> > 39 rows in set (13.32 sec)
> >
> > mysql>
> > ***************************************
> >
> > How is this possible ?? 13.32 seconds ???
> >
> > And this was when I was directly logged on to the Database (via
> Localhost)!
> >
> > When Using my Paradox table this took 1.5 second through my CGI script
> over
> > the Intranet.
> >
> > I have another function in my CGI script sending 2 different SQL
commands
> to
> > the Paradox table.. this entire job completes in less than 3 seconds.
> >
> > In MySQL these 2 job takes more than 30 seconds to complete.
> >
> > What can I do to speed this up ?
> >
> > PS: The DVD table containing the 320 records is the only table on the
> > server. (except from the MySQL database and the empy Test Database).
Both
> > the Paradox Table and the MySQL server is installed on the same
Harddrive
> so
> > harddisk speed shouldn't be a factor.
> >
> > Appreciate any help to solve the above :-)))
> >
> > Kind Regards
> > Thomas Kvamme
> > thokvamm@stripped
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> > http://www.mysql.com/manual.php (the manual)
> > http://lists.mysql.com/ (the list archive)
> >
> > To request this thread, e-mail <mysql-thread130886@stripped>
> > To unsubscribe, e-mail
> <mysql-unsubscribe-bvyas3=comcast.net@stripped>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
> >
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <mysql-thread130890@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-thokvamm=online.no@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
