List:General Discussion« Previous MessageNext Message »
From:Thomas Kvamme Date:January 26 2003 2:06pm
Subject:Re: How to speed things up in MySQL ?
View as plain text  
Zak Greant wrote:

> I am coming in a bit late on this - however, did you send in a copy of
> the table structure?
>
> It sounds like you don't have indexes on the tables.
>
> --zak
>
> On Sun, Jan 26, 2003 at 12:59:31AM +0100, Thomas Kvamme wrote:
> > 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.
>
> --
>  Zak Greant <zak@stripped> | MySQL Advocate |
http://zak.fooassociates.com
>
> MySQL Tip: Impress your friends with nifty MySQL one-liners
>   % mysql --execute "\u mysql; SELECT DISTINCT user FROM user ORDER BY
user;"
>
> Support Global Human Rights - Amnesty International (http://amnesty.org)
>
>

Hi,

I do have indexes...

    ID - PRIMARY KEY

Secondary Indexes:
    Title (Title, ProductionYear)
    NorwegianTitle (NorwegianTitle, ProductionYear)

I even added anotherr Index:

    ProductionYear (ProductionYear)

The GROUP BY still takes more than 10 seconds.

I guess the reason for this is that the I have a some blob fields whitch are
all used.. (each record consist of approx 600 KB...)

But since I donesn't include any of the blob fields in the SELECT statement
I can't see no reason why MySQL should scan all the fields.

****** Table Dump *****
**** 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 ****


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