Hi.
On Sun 2003-01-26 at 15:22:06 +0100, [EMAIL PROTECTED] wrote:
> Benjamin Pflugmann" <[EMAIL PROTECTED]> wrote:
[...]
> Why does MySQL needs to scan the Entire Table (all 200 MB) when I only have
> selected ID & ProductionYear in the SELECT statement ??
Because you have a table with variable-width records and there is no
way to easily find out where a field of a record is stored without
reading all.
> Paradox & BDE only scans/read the selected fields. (otherwise
> Paradox would use the same amount of time of course).
Well, I suggest to stop thinking about low-level properties in the
Paradox way.
That Paradox can do such scans is a side-effect or their low-level
design decisions. Those are almost always trade-offs. I am sure there
other use cases where MySQL is faster than Paradox due to the same
design decisions.
I presume they store blobs in their own tablespace/segment and
therefore can treat the rest of the table as fixed length for the
purpose of this discussion. It's obvious that this will be slower,
when you access these blobs.
[...]
> I added another Index
>
> KEY (ProductionYear).
>
> Well... It gave me 3 seconds... so still the commands takes more than 10
> seconds to complete.
That's still much too slow. The query you posted last time, should
only use the index file to be resolved. Can you post the result of
EXPLAIN for the query?
> 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.
Sounds true.
> 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.
Correct.
> 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....
Ah. As I thought. If that is for all non-fixed-length fields, then
Title (VARCHAR) should be such a field and selecting records based on
Title is slow on Paradox, too? Or do they waste space by treating it
like a fixed CHAR(100) field?
> So taking a full scan doesn't require it to read/scan through Blob fields
> unless they are specified within the SELECT statement.
Well, as I said last time, the way to archieve full speed with other
database engines is to split the table into two, based on size and on
whether the field will be used in the WHERE clause. In your case it
should be enough to move the biggest fields to a seperate table.
> > 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).
It should. There is something else wrong.
Regards,
Benjamin.
> > 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.
--
[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
