MySQL Forums
Forum List  »  MyISAM

Re: how table rows are stored at the file level
Posted by: Rick James
Date: June 17, 2011 07:48PM

No. ALTER is pretty simple-minded
ALTER TABLE t MODIFY COLUMN dummy1 real_name INT ...;
_also_ copies the entire table over.

MyISAM stores the TEXT/BLOB with the record. (InnoDB does something different.)

You could do "vertical partitioning". Store all the "metadata" in one table, with an id (probably AUTO_INCREMENT). Store the blob in another table, together with just the id. This does not benefit you much in the direction you are discussing; instead it has a different benefit...

Often queries need to search a lot of the metadata to find the one blob (image, say) that is desired. When the images are stored with the meta data, the search has to step over them to check more rows. If they are separate, the search part can go faster (at least when the data has to be read from disk). Then when it finds the one (or small number of) rows, it can efficiently JOIN to the image table to find the image.

It's all about "count the disk hits". Let's say you have
* 100 bytes of metadata per image.
* each image is 20K.
* and you need to scan 30 records to find 1 image.
* Let's say disk is fetch 4K at a time.

With the images included in the record, each record is 5 ((20K+100/4K) blocks per record, for a total of 150 blocks (30*5).
With the images separate, the search scans 1 block (30*100/4K), then fetches the image, another 5 blocks. Total 6 blocks.

6 versus 150 -- a big difference in cacheability.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: how table rows are stored at the file level
1982
June 17, 2011 07:48PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.