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

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