Hi Bhavin.
Thanks for taking interest in my problem...
Here is the table definition as requested.
I'm sorry I could take a full dump... Each record consist of approx 600 KB
(just as in the Paradox table) so the file would be very big. (200 MB).
Kind Regards
Thomas Kvamme
[EMAIL PROTECTED]
**** BEGIN ***
-- MySQL dump 9.07
--
-- Host: localhost Database: tdd
---------------------------------------------------------
-- Server version 4.0.9-gamma-nt
--
-- Table structure for table 'dvd'
--
CREATE TABLE dvd (
ID int(11) NOT NULL auto_increment,
Title varchar(100) default NULL,
ProductionYear varchar(4) default NULL,
TagLine varchar(200) default NULL,
NorwegianTitle varchar(100) default NULL,
NorwegianTagLine varchar(200) default '',
Director varchar(100) default '',
Story varchar(100) default '',
Producer varchar(100) default '',
Genre varchar(100) default '',
Production varchar(200) default '',
Distribution varchar(200) default '',
NorwegianDistribution varchar(200) default '',
ScreenFormat varchar(50) default '16:9 Anamorphic Widescreen',
AspectRatio varchar(10) default '2:35:1',
DVDDiscType char(2) default '9',
DVDRegion char(1) default '2',
RunningTime time default '00:00:00',
IMDB varchar(20) default '',
Starring blob,
SoundTracks blob,
Subtitles blob,
SpecialFeatures blob,
Comments blob,
PlotOutline blob,
FullCredits longblob,
Cover longblob,
THXCertified enum('False','True') default 'False',
DDEX enum('False','True') default 'False',
DTS enum('False','True') default 'False',
MPEG enum('False','True') default 'False',
Recommended enum('False','True') default 'False',
Added datetime default '0000-00-00 00:00:00',
Updated datetime default '0000-00-00 00:00:00',
PRIMARY KEY (ID),
KEY Added (Added),
KEY Title (Title,ProductionYear),
KEY NorwegianTitle (NorwegianTitle,ProductionYear)
) TYPE=MyISAM;
***** END ****
----- 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
