5 ms·

I don't understand why MySQL has 9 different storage engines. Would someone mind quickly posting the benefits of that architecture as opposed to focusing on on

I don't understand why MySQL has 9 different storage engines. Would someone mind quickly posting the benefits of that architecture as opposed to focusing on one 'storage engine' and making it really good?

neilc 18y ago
I think the argument is that different domains impose different requirements on the storage engine. The type of data organization I would like to use for a read-mostly web application vs. an OLTP-type transaction processing workload vs. an OLAP-type analysis workload might all differ fairly substantially.
cbrinker 18y ago
Well, I pose to you this question: Would you rather have 1 type of car that the government makes that works pretty reliably and is a middle-ground car, or would you rather have a selection of cars to chose from; sports, truck, sedan, minivan, etc. ? Everyone has different needs. I use MySQL extensively for it's memory-based storage engine, MyISAM for quick & dirty non-escential data i/o, and InnoDB for when data needs to be managed securely for processes. I would use PG for transaction-based data processing in a high-volume situation with multi-processing clusters. Otherwise MySQL works fantastic for all my needs.
- samuel 18y ago
  The flexibility is great, but only if grouped with consistency. No foreign keys on MyISAM but yes on InnoDB? Thanks but no, thanks.
  - thwarted 18y ago
    No disk backed storage with heap tables but yes on InnoDB? Thanks but no, thanks. Uh, the definition of flexibility is lack of consistency between the options. Otherwise, it's a false choice.
- earthboundkid 18y ago
  Car analogies? Well, I pose you this question: Would you rather have a car that runs into a wall and explodes and kills everyone inside or no car at all? The car is like using car analogies on Slashdot and not having a car is like not using car analogies on YC. In conclusion and in summary, go back to Slashdot. KTHXBAI.
- davidw 18y ago
  I've always preferred the bicycle analogy: MyISAM is like a bicycle with no brakes. It goes real fast down hills, unless something goes wrong.
mdasen 18y ago
It isn't about better vs worse. It's about the right tool for a job. For example, if you needed to store key => value pairs, how would you do that? If you use an array (array[0] = ('key', value')), you would have to iterate over the entire array to find the key you're looking for (Order n), but if you use a hash table, you can do key look-ups in constant time (Order 1). MySQL's different storage engines provide similar opportunities to use different ways of storing data for different ends. MyISAM has amazingly fast read speeds, but is really poor under write conditions. Sounds like a crappy storage engine, right? Well, what if you have a table of zip codes and their geo-coordinates? How often are you going to be writing new data to that table? Every couple months in a batch update? So, if you put your postal codes in a MyISAM table you can take advantage of high read rates and ignore the fact that MyISAM is terrible for writes since you don't really write to the table.

neilc 18y ago

I think the argument is that different domains impose different requirements on the storage engine. The type of data organization I would like to use for a read-mostly web application vs. an OLTP-type transaction processing workload vs. an OLAP-type analysis workload might all differ fairly substantially.

cbrinker 18y ago

Well, I pose to you this question: Would you rather have 1 type of car that the government makes that works pretty reliably and is a middle-ground car, or would you rather have a selection of cars to chose from; sports, truck, sedan, minivan, etc. ? Everyone has different needs. I use MySQL extensively for it's memory-based storage engine, MyISAM for quick & dirty non-escential data i/o, and InnoDB for when data needs to be managed securely for processes. I would use PG for transaction-based data processing in a high-volume situation with multi-processing clusters. Otherwise MySQL works fantastic for all my needs.

samuel 18y ago
The flexibility is great, but only if grouped with consistency. No foreign keys on MyISAM but yes on InnoDB? Thanks but no, thanks.
- thwarted 18y ago
  No disk backed storage with heap tables but yes on InnoDB? Thanks but no, thanks. Uh, the definition of flexibility is lack of consistency between the options. Otherwise, it's a false choice.
earthboundkid 18y ago
Car analogies? Well, I pose you this question: Would you rather have a car that runs into a wall and explodes and kills everyone inside or no car at all? The car is like using car analogies on Slashdot and not having a car is like not using car analogies on YC. In conclusion and in summary, go back to Slashdot. KTHXBAI.
davidw 18y ago
I've always preferred the bicycle analogy: MyISAM is like a bicycle with no brakes. It goes real fast down hills, unless something goes wrong.

mdasen 18y ago

It isn't about better vs worse. It's about the right tool for a job. For example, if you needed to store key => value pairs, how would you do that? If you use an array (array[0] = ('key', value')), you would have to iterate over the entire array to find the key you're looking for (Order n), but if you use a hash table, you can do key look-ups in constant time (Order 1). MySQL's different storage engines provide similar opportunities to use different ways of storing data for different ends. MyISAM has amazingly fast read speeds, but is really poor under write conditions. Sounds like a crappy storage engine, right? Well, what if you have a table of zip codes and their geo-coordinates? How often are you going to be writing new data to that table? Every couple months in a batch update? So, if you put your postal codes in a MyISAM table you can take advantage of high read rates and ignore the fact that MyISAM is terrible for writes since you don't really write to the table.