> >>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 <[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 > --------------------------------------------------------------------- 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
