Add an index to ProductionYear. If that doesn't speed things up to significantly less than a second from the command line, I'd check how MySQL is set up. Fred Steinkopf ----- Original Message ----- From: "Thomas Kvamme" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] > > > --------------------------------------------------------------------- > 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 <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
