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

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