Benjamin Pflugmann" <[EMAIL PROTECTED]> wrote:
> Hi.
>
Hi Benjamin :-)
> 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. ;)
>
Sorry about that.. but since the Paradox table have the same size and
aprarently not the problem so I didn't think it was necessary.
>
> 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.
>
Why does MySQL needs to scan the Entire Table (all 200 MB) when I only have
selected ID & ProductionYear in the SELECT statement ??
Paradox & BDE only scans/read the selected fields. (otherwise Paradox would
use the same amount of time of course).
>
> Don't know why Paradox is faster. Maybe it can make partial use of one
> of the combined indexes which contain ProductionYear.
>
I added another Index
KEY (ProductionYear).
Well... It gave me 3 seconds... so still the commands takes more than 10
seconds to complete.
I tested this on Paradox as well... I removed ALL index and tried again...
still the same greate speed even without indexes..... and I think - this
becasue it doesn't need to read/scan all the fields not specified in the
SELECT statement.
I guess it would be too much to ask the MySQL development team to
re-organize MySQL on a file-level *lol* But I think the problem here is
that the table consist of 1 big file.... that way MySQL must to read the
entire record so the file pointer is at the beginning of the next record and
ready to read it.
In Paradox all Non-Fixed-Length fields such as Blob fields etc. are stored
in a separate file... while the primary file only contains pointers to the
Dynamic file if you like....
So taking a full scan doesn't require it to read/scan through Blob fields
unless they are specified within the SELECT statement.
Kind Regards
Thomas Kvamme
[EMAIL PROTECTED]
>
> 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.
>
Tested this... didn't make any difference (even on MySQL nor Paradox).
>
> 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
>
>
---------------------------------------------------------------------
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
