Heap table limitations

------=_NextPart_000_0056_01C43E4C.DDD5CEF0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

We want to convert a 200+ meg table to a heap table. We've gotten pretty
good performance converting from InnoDb to MyIsam table but converting to a
heap table would give an even bigger performance boost.



Does making a heap file this large make any sense?



Are there size limitations to heap tables?



TIA



Dan


------=_NextPart_000_0056_01C43E4C.DDD5CEF0--

RE: Heap table limitations

You can't do efficient ranging on a HASH indexed column without a table
scan...

Select * from heap_table where indx_col between 247 and 258

This would table scan your 200meg table even if "indx_col" is a PRIMARY
KEY, using the default hash key. Hash key was the only choice prior to
4.1, but now you can use BTREE for HEAP tables. Again, it depends on how
you use your tables.

If your server blinks, scheduled or otherwise, you loose all of your
table contents...forcing you to reload your data.

Make sure you place a practical MAX_ROWS=3D on your create table to
prevent your table from accidentally eating all of you're available
memory.

Ed

-----Original Message-----
From: Daniel Cummings [mailto:DanielCummings@BrickSoftware.com]=20
Subject: Heap table limitations

We want to convert a 200+ meg table to a heap table. We've gotten
pretty
good performance converting from InnoDb to MyIsam table but converting
to a
heap table would give an even bigger performance boost.

Does making a heap file this large make any sense?

Are there size limitations to heap tables?
TIA
Dan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql@m.gmane.org

Re: Heap table limitations

Daniel Cummings wrote:
> We want to convert a 200+ meg table to a heap table. We've gotten pretty
> good performance converting from InnoDb to MyIsam table but converting to a
> heap table would give an even bigger performance boost.
>

You think it would, or it already has in your testing? Heap vs. MyISAM is not
that much of a speed up on an OS that caches well even in the ideal situation
for HEAP.

>
>
> Does making a heap file this large make any sense?

Actually, there is no file. It is all in memory. But if you have the RAM the
size should not be a problem.

>
> Are there size limitations to heap tables?

Amount of RAM + max_heap_table_size setting in the server.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org