Hello,
I was wondering what is the performance gain typically realized when one uses MySQL HEAP tables compared to filesystem or regular disk-based tables? Is it a lot faster?
I read in the mysql docs that:
(1) HEAP tables can only use whole keys to search for a row; compare this to MyISAM tables where any prefix of the key can be used to find rows.
(2) HEAP tables can't use indexes for ORDER BY selects.
Now, I use a lot of LIKE 'key_prefix%' and ORDER BY key stuff in SQL and I usually add indexes to those columns.
To my understanding, using HEAP tables destorys any performance gain from adding these indexes for LIKE and ORDER BY selects
since they will not be used. Is this true?
The $64,000 question, given your experience, should I create my cache using HEAP tables even though indexes will not be utilized in the search of rows?
Thanks!
I was wondering what is the performance gain typically realized when one uses MySQL HEAP tables compared to filesystem or regular disk-based tables? Is it a lot faster?
I read in the mysql docs that:
(1) HEAP tables can only use whole keys to search for a row; compare this to MyISAM tables where any prefix of the key can be used to find rows.
(2) HEAP tables can't use indexes for ORDER BY selects.
Now, I use a lot of LIKE 'key_prefix%' and ORDER BY key stuff in SQL and I usually add indexes to those columns.
To my understanding, using HEAP tables destorys any performance gain from adding these indexes for LIKE and ORDER BY selects
since they will not be used. Is this true?
The $64,000 question, given your experience, should I create my cache using HEAP tables even though indexes will not be utilized in the search of rows?
Thanks!