List:General Discussion« Previous MessageNext Message »
From:DL Neil Date:October 24 2001 12:33pm
Subject:Re: Slow multi-column index
View as plain text  
Hi Phil,
I suspect you have already answered your own question!
Warning: high ignorance factor - am currently researching indexes for myself - if I can't
figure it out for myself, you
can help me with that later...

When the SELECT can, it will answer a query from a scan-of/data-retrieval-from the index
file(s) alone. Under such
conditions it will respond v.quickly - this applies to two of your examples:

   SELECT count(*) FROM MAIN WHERE...
   SELECT fileDate...

the latter being fulfilled from your

   INDEX (fileDate),

- which incidentally looks superfluous because wouldn't any query on the fileDate index be
equally well covered under
the previously defined

   INDEX (fileDate, filePath, mediaType),

You point to the above being fine and dandy, but performance problems resulting whenever
the actual data table is
required/accessed to provide the data. In this there is only evidence from one
data-selecting query (ie a SELECT that
cannot be entirely satisfied by data held in the indexes). Do you experience equivalently
long response times from other
such queries?

Another part that you may have self-answered was to do with variable length fields/rows
and rate of inserts. Is it worth
trying a reorg (ie to produce a 'state' of zero inserts) and then comparing 'before' and
'after' response times? [really
high ig-factor here! such an action is usually designed to improve the b-trees in the
indexes, and I'm not at all sure
of its effect on the layout of a data table with heavy use of variable length fields]

Because of exactly this 'fear' (if not 'issue') a device I have used in the past (but not
in MySQL) was to move the
variable length stuff to a separate table, and keep the rest/not too much variable-len in
the 'main table'. Ok the
absolute one-to-one relationship means that this involves de-normalising the database, but
such would be 'legitimised'
if it helps cope with a performance bind (don't tell the RDB-police though!)

Make any sense?
=dn


> > >>So, is it *really* the variable length row lookup that's taking all the
> time
> > here ?<<
> >
> > I don't see how. 18000 record is nothing and varchar searches are pretty
> fast.
> > On top of that, you first search is on the date which would be extremelly
> > fast and narrow the next searches to many less records.
> >
> > Are you sure it is the database that is slow to retrieve your result or
> could
> > it be with other code you have around the database but within you time
> > markers?
>
> Hi - yes, it really is the SELECT statement that's taking the time. The
> timings I gave are directly from the mysql command line, ie. reported by
> mysql.exe itself, not my code. By the way, only around 60 rows are returned
> by the query.
>
> I'm sure you're right, that VARCHAR searches are fast, since the 2nd query
> (SELECT fileDate...) is near instantaneous and is searching on the same
> VARCHAR columns as the slow one.
> The difference between the slow & fast queries seems to be the fact that the
> slow one is having to retrieve the row data from the table rather than the
> index itself.
>
> When an index 'returns' a query match, does it also contain some sort of
> lookup into the table to allow the row itself to be retrieved ? Or, if rows
> are variable length, does MySQL have to do a linear scan through the
> database to find the row ? That's what I'm wondering because of the
> reference manual warning about variable length rows.
>
> Thanks
>
> >
> >
> > On Wednesday 24 October 2001 06:52 am, Philip White wrote:
> > > Hello all
> > >
> > > Grateful for any help on this -
> > >
> > > I have a table (definition for MAIN below) with a number of indexes
> > > corresponding to various frequently used queries.
> > > A SELECT statement using one of the multi-column indexes is running very
> > > slow if it retrieves data that is not in in the index itself. For
> example,
> > > a "SELECT count(*) FROM MAIN WHERE..." takes 0.04 seconds, but "SELECT
> > > fileName from MAIN WHERE..." takes 20 seconds. The table currently has
> > > around 18000 rows, and the DB is around 400MB.
> > >
> > > I'm wondering whether this has something to do with the following words
> of
> > > wisdom in the reference manual:
> > > "For tables that changes a lot you should try to avoid all VARCHAR or
> BLOB
> > > columns. You will get dynamic row length as soon as you are using a
> single
> > > VARCHAR or BLOB columns."
> > >
> > > but I don't see why this should matter. Why does it matter that row
> lengths
> > > are dynamic ? Surely the index doesn't just specify a row "index" that
> is
> > > multiplied by the row length ? Surely it contains some sort of row data
> > > offset to allow for variable row lengths.
> > >
> > > If any use, here's the table definition:
> > >
> > > CREATE TABLE MAIN
> > > (
> > >  id    INT(4) AUTO_INCREMENT PRIMARY KEY,
> > >  addedDateTime DATETIME NOT NULL,
> > >  filePath  VARCHAR(100) NOT NULL,
> > >  fileDateTime DATETIME NOT NULL,
> > >  fileDate  DATE NOT NULL,
> > >  fileName  VARCHAR(255) NOT NULL,
> > >  mediaType  VARCHAR(100) NOT NULL,
> > >  key1   CHAR(32),
> > >  key2   CHAR(32),
> > >  language  CHAR(4),
> > >  headline  BLOB,
> > >  enStory   TEXT,
> > >  searchSlug  TEXT,
> > >  searchCodes  TEXT,
> > >  fileData  LONGBLOB,
> > >
> > >  INDEX (filePath, mediaType),
> > >  INDEX (addedDateTime, fileDateTime, fileDate, mediaType, filePath),
> > >  INDEX (fileDate, filePath, mediaType),
> > >  INDEX (fileDate),
> > >  INDEX (fileName),
> > >  INDEX (mediaType),
> > >  INDEX (key1),
> > >  INDEX (key2),
> > >  FULLTEXT (enStory),
> > >  FULLTEXT (searchSlug),
> > >  FULLTEXT (searchCodes)
> > > ) TYPE=MYISAM;
> > >
> > > and the slow query is something like:
> > >
> > > SELECT fileName FROM MAIN WHERE (fileDate='20011021') AND
> > > (filePath='OLWWPICGLSP) AND (mediaType='TEXT');
> > > (~20 seconds)
> > >
> > > EXPLAIN says this will use the 3rd index (fileDate, filePath, mediaType)
> > > which looks fine. If the query is changed to:
> > >
> > > SELECT fileDate FROM MAIN WHERE (fileDate='20011021') AND
> > > (filePath='OLWWPICGLSP) AND (mediaType='TEXT');
> > > (0.02 seconds)
> > >
> > > This is fine because it can get 'fileDate' from the index itself.
> > >
> > > So, is it *really* the variable length row lookup that's taking all the
> > > time here ?
> > >
> > > Thanks for any help on this,
> > > Phil White
> > >
> > >
> > >
> > > ---------------------------------------------------------------------
> > > 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 <mysql-thread88849@stripped>
> > > To unsubscribe, e-mail
> <mysql-unsubscribe-kodrik=zc8.com@stripped>
> > > 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 <mysql-thread88851@stripped>
> > To unsubscribe, e-mail
> <mysql-unsubscribe-pwhite=dircon.co.uk@stripped>
> > 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 <mysql-thread88855@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-PHPml=dande.homechoice.co.uk@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>

Thread
Slow multi-column indexPhilip White24 Oct
  • Re: Slow multi-column indexKodrik24 Oct
  • Re: Slow multi-column indexPhilip White24 Oct
  • Re: Slow multi-column indexDL Neil24 Oct
  • RE: Slow multi-column indexSteve Meyers24 Oct