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