InnoDB vs MyISAM Discussion

bajaber wrote: The possibility of a 1 in a million chance of the error happening does not justify the time and effort to redevelop the system

You underestimate the issue. Every serious DBMS supports transactions, do you think they implemented transactions just for fun?

bajaber wrote: Exactly. Keyword, IF.

They don't cause any problem if they aren't supported.

bajaber wrote: We already have reliability. Do you know of anyone who has had problems because of (lack of) transactions in OC?

Not really, for the reasons that I've already explained. Do you need to see burnt people to understand that the fire is hot?

ekerazha wrote:

bajaber wrote: The possibility of a 1 in a million chance of the error happening does not justify the time and effort to redevelop the system

You underestimate the issue. Every serious DBMS supports transactions, do you think they implemented transactions just for fun?

bajaber wrote: Exactly. Keyword, IF.

They don't cause any problem if they aren't supported.

bajaber wrote: We already have reliability. Do you know of anyone who has had problems because of (lack of) transactions in OC?

Not really, for the reasons that I've already explained. Do you need to see burnt people to understand that the fire is hot?

On the contrary, you are overselling the importance.

1. They were implemented for when they are needed, like banking systems, etc. Implementing them in every scenario is indeed for fun.

2. Great, so we will stick to what we have now.

3. I need sufficient proof that it is worth having a fire brigade in a town which has only 2 people staying in it.

bajaber wrote: On the contrary, you are overselling the importance.

1. They were implemented for when they are needed, like banking systems, etc. Implementing them in every scenario is indeed for fun.

2. Great, so we will stick to what we have now.

3. I need sufficient proof that it is worth having a fire brigade in a town which has only 2 people staying in it.

1. E-commerce is a typical scenario where transactions are useful, indeed Magento implements them (Magento is used by Samsung, Lenovo etc.).

2. Great, so you don't even know how transactions work.

3. No you just need to troll. As a computer engineer, I do already know that transactions are a requirement for a decent reliability. I'm not here to argue about this with you, this is already obvious for competent people. Originally, I just asked if v1.5.0 will use transactions, now we know that it won't. Now we can be friends as before.

Interesting ...

Which tables need inno db?
Surely not all. Therefore, based on the number of tables having the option of somebody providing a "extension" might be the solution to this.

Another question I have is, does PDO support this out of the box?

SapporoGuy wrote: Another question I have is, does PDO support this out of the box?

Of course, method beginTransaction() etc.

ekerazha wrote:

bajaber wrote: On the contrary, you are overselling the importance.

1. They were implemented for when they are needed, like banking systems, etc. Implementing them in every scenario is indeed for fun.

2. Great, so we will stick to what we have now.

3. I need sufficient proof that it is worth having a fire brigade in a town which has only 2 people staying in it.

1. E-commerce is a typical scenario where transactions are useful, indeed Magento implements them (Magento is used by Samsung, Lenovo etc.).

2. Great, so you don't even know how transactions work.

3. No you just need to troll. As a computer engineer, I do already know that transactions are a requirement for a decent reliability. I'm not here to argue about this with you, this is already obvious for competent people. Originally, I just asked if v1.5.0 will use transactions, now we know that it won't. Now we can be friends as before.

Yeah, I guess so. Coz my job as an SQL Developer has nothing to do with transactions. You got your answer long ago and you kept annoyingly trying to force your opinions on others, and I am the troll? Haha.

You are right, ecommerce applications are the ones that need transactions. No doubt about that. But not all ecommerce applications - which is what you fail to see. Just like, following up on the example of the fire, fire brigades are needed to put out fires, but definitely not the one on your matchstick. Payment processing is the critical aspect in ecommerce that needs transactions, and this is not directly handled by OC. Part of good development practices is knowing when to use a particular technology for optimum results. You cannot apply everything you learned in your Beginners Guide to SQL on every project you do. Any sensible person knows that. Realiability is key, and transactions are one way to have that. The OC team has not decided to use that approach. They are using a different approach probably - not sure what that is, but it sure as hell works. And that's very good, coz I am yet to hear of any reliability issues with OC. Far from it, it's the least of all suites that I have heard reliability complaints about.

Yes, Magento has it, and as someone posted earlier, go use Magento. As I mentioned, every scenario has different requirements. It's upto the project team to decide what is best, based on their requirements. Am sure the OC team has thought about that and they have decided transactions are not necessary, and one of the developers has even explained to you why. They have deemed it not necessary for reasons they mentioned earlier, and won't be for the foreseeable future. I hope that puts this to rest.

bajaber wrote: Blah blah blah

Cool story, bro

so lets humor ourselves

Does innodb need to be on the entire db or just some tables?
Can a table be converted without losing data?
Given this code: Is innodb adoption as simple as prefixing it with
$this->db->query("START_TRANSACTION");
and ending it with
$this->db->query("Commit");
?

Qphoria wrote: Does innodb need to be on the entire db or just some tables?

You can have mixed tables but usually I don't recommend it. I think you should first try an InnoDB only approach, don't overrate the performance difference between it and MyISAM. Just avoid COUNT(*) without WHERE clause (like I said before).

Can a table be converted without losing data?

Yes

Given this code:

Code: Select all

$this->db->query("DELETE FROM `" . DB_PREFIX . "order` WHERE order_id = '" . (int)$result['order_id'] . "'");
$this->db->query("DELETE FROM " . DB_PREFIX . "order_history WHERE order_id = '" . (int)$result['order_id'] . "'");
$this->db->query("DELETE FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$result['order_id'] . "'");

Is innodb adoption as simple as prefixing it with
$this->db->query("START_TRANSACTION");
and ending it with
$this->db->query("Commit");
?

That's one of the features of InnoDB: transactions.
Yeah, it's that simple, however that's a basic usage, refer to the MySQL docs about other statements like ROLLBACK (to manually rollback) etc.

1.) Why don't you recommend mixing?

2.) If it's basically that simple to make the changes.
Couldn't the mysql class file be adapted instead of going through all the files?

3.) If you're doing all the files, you might as well make a change to db abstraction layer ...
Which brings up the question of db abstraction:

3.1) What are the performance hits?
3.2) Which package is recommend?
3.3) Aren't normal php calls better and faster?

SapporoGuy wrote: 2.) If it's basically that simple to make the changes.
Couldn't the mysql class file be adapted instead of going through all the files?

Both. You'd still go through the various classes from the Opencart model directories and check all the SQL to make sure it's standard compliant. It's not rocket science, but it would still take a few days to do so. It should be possible to use the SQL:1999 or SQL-92 standard syntax for the SQL.

I don't recommended the usage of an ORM, they tend to be cumbersome and easily become too difficult when it comes to mapping even simple things like multiple JOINS, inner SELECTs, or GROUP BY to appropriate ORM.

ouch, my head hurts trying to understand that ...
Did some googling but still not getting a grasp on the ease of use versus best practice or even the benefit to stay simple ...

will do more study

I did find this:
http://my.opera.com/zomg/blog/2007/09/0 ... ing-propel

Seems like it would take care of some of the complexity problems ...

Well, there's a simple way to add transaction, you just start a transaction when detect an "insert or delete or update" and end it when you change the called method or when you finish, all you need is add 3 or 4 line of code in the "driver" class.

Simple like a pancake! No pretty, No Apply to all thing, but functional.

And why the hell OC don't support prepared statement, placeholder and mptt, just kidding... 20 page more with this...

At the risk of jumping into on on-going discussion at a late point, I'd like to make some (hopefully useful to you) comments:

• InnoDB should work with ALL queries that work with MyISAM. It is a different engine, not SQL syntax. The MySQL application determines the syntax.
• Using MyISAM has its advantages, as does using InnoDB. It depends what you value more (see below).
• In it not necessary to use ORM with InnoDB. Regular SQL works just as well.
• It is possible to have a mix of engines, where specific tables use specific engines. Converting a table from one engine to another is a matter of a single statement (ALTER TABLE table_name ENGINE = engine_name).

Having worked with various databases (Sybase, Oracle, MySQL, MSSQL, Access) in applications ranging from simple spreadsheet to a fully distributed global app, the benefit of consistency and resiliency far outweigh the (usually small) performance hit.

Main differences between the two engines:

MyISAM limitations

• No Foreign keys and cascading deletes and updates
• No rollback abilities
• No transactional integrity (ACID compliance)

InnoDB limitations

• No full text indexing
• Cannot be compressed for fast, read-only

While most stores involve reading the database, usually the information required is already cached in the database memory. This makes the underlying engine irrelevant. Even when requiring a physical read of data, most stores do not have the size (millions of items) and customer hits (thousands of hits a second) that will make any performance differences noticeable to the user.

However, the importance of ensuring that updates to stock, captures of orders, and other writes to the database are consistent and atomic, is of paramount importance. The worst thing that can happen to a store is that a customer thinks he/she placed an order, but the order was not recorded in the database. There are ways to ensure this does not happen within the application, but then the application code begins to compensate for the database's deficiency. It is best to use the database with its focus on this functionality and capture of all such cases.

Performance aside, the availability of foreign keys helps ensure that the design the developer had in mind is maintained even if the code has bugs and tries to create improper data relationships.

All these little things make InnoDB a better choice than MyISM if you write data of any significance.

Just my 2 cents...

SapporoGuy wrote:1.) Why don't you recommend mixing?

Mainly because it's a waste of memory, as you run two engines instead of one.

3.) If you're doing all the files, you might as well make a change to db abstraction layer ...

If you ask me, PDO all the way. But this isn't the point here.

After Magento (which uses InnoDB), the new PrestaShop 1.4 added InnoDB support (I didn't check if they also take advantage of transactions).

Well it still isn't planned for OpenCart at this time

I remember awhile ago I downloaded PS just to see what all the fuss was about, and I installed it once with InnoDB and then a second time with MyISAM.

From a purely non-scientific observation, InnoDB seemed like it slowed down the page loading speed of my shop a bit. Again, non scientific, but just my personal observation.

The "read" slowing down is mostly negligible, however the "write" speed is much better on InnoDB because MyISAM uses a table lock when writing. But above all, InnoDb is a magnitude more reliable.

Yeah but with most web stores people are just browsing your site anyway. Unless you're running hundreds or thousands of transactions a day, I don't really see how using InnoDB would benefit you, especially since the "read" makes you site a bit slower.

I can understand InnoDB for websites that require heavy write like banking systems, amazon.com, ebay, etc., but a small to medium sized business running a web store, are they really gonna have a lot of write intensive activity? Would these sites benefit from having a faster write but a slower read?