Hi Jeremy, Sorry, it seems like I'm saying this a lot lately. Is it not true that if the whole table will fit in [free] RAM, that the OS will cache the file data and there is no need for a RAM disk. I don't really see how performance would be any different than using a RAM disk. Either way, you will still have the overhead of the filesystem calls, even if data isn't actually read from disk, unlike with a HEAP table.
Jonathan, I think a packed table would actually make things slower once the data is cached (overhead of unpacking the data). It's just faster when the data has to be *physically* read from disk, since the data is smaller. If disk space isn't an issue and the table doesn't have any TEXT/BLOB columns, make the rows fixed length if they aren't (change VARCHAR > CHAR). This will give a small performance improvement. Also, if you don't have any TEXT/BLOB columns, you could load the data into a HEAP table, which *may* make reads *slightly* faster -- depending on your queries. Remember, with HEAP tables, indexes can't be used in all cases that MyISAM can. You can only search on indexes with =, <=>, IS NULL, and IN (); no range searches with <, >, BETWEEN, etc.; and you can only use the full index length, no prefixes. However, none of this is true in 4.1+, since you can have BTREE indexes with HEAP tables, not just HASH. :-) But you know what should actually be the best thing for your read-only table? MySQL 4's query cache! :-) Have you thought about this? Or do your queries differ too much that the cache can't be used? Hope this helps. Matt ----- Original Message ----- From: "Jeremy Zawodny" Sent: Saturday, November 08, 2003 11:48 PM Subject: Re: Strategies for optimizing a read-only table > On Tue, Nov 04, 2003 at 08:45:08PM -0500, Jonathan Terhorst wrote: > > > > I could have sworn I posted this once before, but apparently it got > > lost somewhere. Apologies if you're seeing this twice: > > > > I'm wondering what I can do with MySQL to optimize reads (SELECTs) > > on a read-only table where data will never be INSERTed or > > UPDATEd. Okay, that's not entirely correct--the database will be > > rebuilt every night but it's small (~20,000 rows) and all the > > writing will take place at once, when the DB is offline to users. In > > contrast we anticipate read activity on the DB to be high, making it > > worth putting some thought into this. So far my only thoughts have > > been a) myisampack and b) to index every single column that our > > application searches on, since the calculations needed to build said > > indices can be performed once and forgotten. (Disk space isn't > > really an issue but myisampack is said to speed up individual row > > retrieval.) > > > > Any other ideas? I've searched for a way to manually mark MySQL > > tables read-only, but to no avail. Thanks, > > Will the whole table fit comfortable in RAM? If so, you could store > it in a ram disk to prevent disk I/O from ever getting in the way. > > Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
