List:General Discussion« Previous MessageNext Message »
From:Thomas Kvamme Date:January 25 2003 11:59pm
Subject:Re: How to speed things up in MySQL ?
View as plain text  
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
>
>

Thread
How to speed things up in MySQL ?Thomas Kvamme25 Jan
  • Re: How to speed things up in MySQL ?Bhavin Vyas25 Jan
  • Re: How to speed things up in MySQL ?Benjamin Pflugmann25 Jan
    • RE: How to speed things up in MySQL ?Loren McDonald26 Jan
  • Re: How to speed things up in MySQL ?Thomas Kvamme26 Jan
    • RE: How to speed things up in MySQL ?Markus Gieppner26 Jan
    • Re: How to speed things up in MySQL ?Benjamin Pflugmann26 Jan
  • Re: How to speed things up in MySQL ?Thomas Kvamme26 Jan
  • Re: How to speed things up in MySQL ?Frederick L. Steinkopf26 Jan
  • Re: How to speed things up in MySQL ?Thomas Kvamme26 Jan
    • Re: How to speed things up in MySQL ?Sergei Golubchik26 Jan
  • Re: How to speed things up in MySQL ?Thomas Kvamme26 Jan
    • Re: How to speed things up in MySQL ?Benjamin Pflugmann26 Jan
  • Re: How to speed things up in MySQL ?Thomas Kvamme26 Jan
Re: How to speed things up in MySQL ?Steven Roussey26 Jan
  • Re: How to speed things up in MySQL ?Thomas Kvamme27 Jan