Hi.
On Sun 2003-01-26 at 00:26:32 +0100, [EMAIL PROTECTED] wrote:
> 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).
Well, you should have told us that to begin with. ;)
600KB/record is not that usual. You are querying for ProductionYear
but have no usuable index for it. That results in a full table scan,
which has to read 200MB from disk, which is not unreasonble to take 13
secs, depending on your hardware. Btw, this also means that you can
disregard my comment from the other mail about disk usage. I really
did not expect your rows being so large without you mentioning it.
Don't know why Paradox is faster. Maybe it can make partial use of one
of the combined indexes which contain ProductionYear.
Anyhow, creating an index on ProductionYear will do the job, so that
the query runs fast. I am not sure if referring to ID in count(ID) is
a problem. If so, replace it by count(*) additionally.
If you often have to query on non-indexed fields, the usual solution
is to split up the table into two: one containing the fields on which
you want to query, the other the fields which you only query by
primary key (probably most blob fields). And use a join if you want
some blob depending on one of the "query" fields.
This way the table size to read for full table scans will be much
smaller. Btw, this is not MySQL-specific, but true for any
database. It would also get the times with Paradox in the sub-second
range.
HTH,
Benjamin.
PS: Why don't you use a (small)int for ProductionYear?
> CREATE TABLE dvd (
> ID int(11) NOT NULL auto_increment,
> Title varchar(100) default NULL,
> ProductionYear varchar(4) default NULL,
[...]
> Starring blob,
> SoundTracks blob,
> Subtitles blob,
> SpecialFeatures blob,
> Comments blob,
> PlotOutline blob,
> FullCredits longblob,
> Cover longblob,
[...]
> PRIMARY KEY (ID),
> KEY Added (Added),
> KEY Title (Title,ProductionYear),
> KEY NorwegianTitle (NorwegianTitle,ProductionYear)
> ) TYPE=MyISAM;
> ***** END ****
[...]
> > > mysql> select count(id) as Films, ProductionYear from DVD
> > > -> group by ProductionYear
> > > -> order by ProductionYear desc;
[...]
--
[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
