MySQL Heap Tables / Making MySQL Faster :-)

superwormy

Just gonna link over here... you can respond in this thread though:

http://forums.anandtech.com/messageview.cfm?catid=33&threadid=996367

And this one too:

http://forums.anandtech.com/messageview.cfm?catid=33&threadid=996323

dopey

Say I have 3 or 4 really really often used ( for SELECTs only ) tables in MySQL, woudl it be a good idea to, whenever the MySQL server starts up, take all the data from those tables ( MyISAM ) and make 3 or 4 new HEAP tables to do the SELECTs from?

Can't see why that wouldn't work, but if you're using MySQL >= 4 you could try the query cache feature. You may find this will fit in without any extra code.

See: http://www.mysql.com/doc/en/Query_Cache.html

Cheers,
Andy.

superwormy

Will it improve performance any though...?

dopey

Depends on how often you update the tables and the queries you run e.g. if you run:

SELECT * FROM tablename WHERE IDX = value

the result is stored in the cache.

Any subsequent query that is exactly the same will not be re-parsed and run, but will be returned directly from the cache.

Therefore, yes it will generally be faster, as long as the tables are not constantly updated. This is because on each update for the table, the cache is cleared.

I guess the answer is give it a try! It certainly shouldn't be any slower...

superwormy

Aight, I guess I'll just have to do that :-)

The tables I'd use it on would be queried for strictly SELECTs, and NEVER updated. I would do updates to another table and then recreate the HEAP tables later when I had an update to do.

I'll give it a try and see what happens I guess :-)