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" <[EMAIL PROTECTED]> To: "Thomas Kvamme" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> 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" <[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 > > --------------------------------------------------------------------- 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
