>>>>> "Mark" == Mark Papadakis <markp@stripped> writes:
Mark> Hi!
Mark> "One can create in-memory HEAP tables which are extremely fast for lookups.
Mark> "
Mark> Say I have table with over 500.000 rows, can I use this feature to create
Mark> in-me heap tables for
Mark> mem instead of disk lookups thus achieving supreme perfomance and speed? If
Mark> that's so then I
Mark> am going to build a statue for mySQL guys, it is what I 've been dreaming
Mark> for!
Yes; You can fill your memory with HEAP tables if you like :)
From the (recently updated) MySQL manual:
--------
Here is some things you should consider when you use `HEAP' tables:
* You should always use specify `MAX_ROWS' in the `CREATE'
statement to ensure that you accidently use all memory.
* Indexes will only be used with `=' and `<=>' (but are VERY
fast).
* `HEAP' tables uses a fixed record length format.
* `HEAP' doesn't support `BLOB/TEXT' columns.
* `HEAP' doesn't support `AUTO_INCREMENT' columns.
* `HEAP' doesn't support an index on a `NULL' column.
* You can have non-unique keys in a `HEAP' table (not that
normal with hashed tables).
* `HEAP' tables are shared between all clients (Just like any
other table).
* Data for `HEAP' tables are alloced in small blocks. The
tables are 100 % dynamic (on inserting). No overflow areas
and no extra key space is needed. Deleted rows are put in a
linked list and will be reused when you insert new data into
the table.
* To free memory, you should execute `DELETE FROM heap_table' or
`DROP TABLE heap_table'.
----------
Hope this helps...
Regards,
Monty
