Page 1 of 5

InnoDB vs MyISAM Discussion

Posted: Sun Jan 02, 2011 12:33 am
by Qphoria
ekerazha wrote:Does it use db transactions? This is critical.
wha?

Re: FYI: Upcoming Changes in 1.5.0

Posted: Sun Jan 02, 2011 1:55 am
by Xsecrets
Qphoria wrote:wha?
lmao that was my exact reaction, but I decided to keep it to myself. :laugh:

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 12:20 am
by ekerazha
Qphoria wrote:wha?
Database transactions

http://dev.mysql.com/doc/innodb/1.1/en/ ... ransaction

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 12:26 am
by Xsecrets
ekerazha wrote:
Qphoria wrote:wha?
Database transactions

http://dev.mysql.com/doc/innodb/1.1/en/ ... ransaction
from the article
as implemented by InnoDB
opencart uses myism so no no transactions, and I'd hardly call it crititcal as practically no other ecommerce application does it either.

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 12:32 am
by ekerazha
Xsecrets wrote: from the article
as implemented by InnoDB
opencart uses myism so no no transactions, and I'd hardly call it crititcal as practically no other ecommerce application does it either.
Yeah, you need InnoDB for transactions with MySQL.
This is critical because integrity is critical for ecommerce applications. Indeed, Magento uses transactions.

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 2:05 am
by Qphoria
then use magento

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 2:13 am
by ekerazha
Qphoria wrote:then use magento
Yeah, I like OpenCart, it can be fine for small needs, but for serious business integrity is critical so I'll go for Magento. Thank you for your time.

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 2:35 am
by SapporoGuy
Postgres can do that, but many people have only access to mysql on hosted servers.
Even if Magneto offers it, remember to ask you hosting service if they do.

If you do your own servers, you're set!
You even tune your server to get more juice out it!

Problem is that most of the carts on the net try to go for the 90% percentile for ease of installation.

@ PDO/xPDO
This is one reason why brought the PDO question.
You add a layer to the db but gain:
abstraction, easy to read sql queries, somewhat cleaner code, AND you gain access to several other DBs other than mysql.

Which brings up the point.
Is mysql really the best basket to put all your eggs in?

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 2:43 am
by ekerazha
SapporoGuy wrote:Postgres can do that, but many people have only access to mysql on hosted servers.
Yes, but OpenCart uses MySQL, so adding InnoDB support (to use transactions) would be easier than adding PostgreSQL support.

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 2:48 am
by SapporoGuy
Can you explain in more detail?


@ ease of transition
Not sure, since I'd know what is required to support inno db.
However, I do know that the cart basically would need ever sql query re-written to move to PDO or another adbstraction system.

@ once again
I don't know many hosting services that support inno db for mysql and to add to the fun.
Oracle might just F*uck us all and say "pay royalties".

Which is why the RoR is really keen on moving to mysql lite or some other "open source" DB.

Still comes down to hosting service for most people.

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 2:52 am
by Qphoria
opencart suports many dbs technically.. look in the db folder.. there are different classes for db.. at the moment none of them are working except mysql but it should (theoretically) be as simple as setting up the main class for the individual db

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 3:07 am
by ekerazha
SapporoGuy wrote:@ once again
I don't know many hosting services that support inno db for mysql and to add to the fun.
Every hosting service that I've used in the last 6 years allows for InnoDB tables.
However, you can easily check if InnoDB is available.

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 3:24 am
by SapporoGuy
theoretically, yes.

I'm not as knowledgeable as Xsecrets on the db side of things so ...
But I'd still imagine that a fair number of the sql queries might not work due to slight differences between the various DB distros.

@ ekerazha
What do you need to change to make the current sql queries support Inno DB?
I don't have the time at the moment to google and study this and you seem to know the difference.
Could you educate me?

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 3:41 am
by ekerazha
SapporoGuy wrote: What do you need to change to make the current sql queries support Inno DB?
Basically nothing, but avoid things like COUNT(*) queries without WHERE clause, because they are slow on InnoDB (I don't know if OpenCart uses them).
Also, you have to change something if you want to take advantage of things like transactions, using START TRANSACTION, COMMIT etc. statements.

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 3:48 am
by Qphoria
opencart does use both of those

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 3:53 am
by ekerazha
Qphoria wrote:opencart does use both of those
What?

E.G.

SELECT COUNT(*) FROM table WHERE value=1 <--- OK

SELECT COUNT(*) FROM table <--- Very slow on InnoDB, COUNT(*) without WHERE clause

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 4:08 am
by Qphoria
oh.. no .. opencart doesn't do any select count without a where clause so that would be ok

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 4:17 am
by Xsecrets
Qphoria wrote:opencart suports many dbs technically.. look in the db folder.. there are different classes for db.. at the moment none of them are working except mysql but it should (theoretically) be as simple as setting up the main class for the individual db
well yes the classes are there, but I'm 99% certain that if you tried to use it on another db things would break all to pieces. I'm not overly familiar with other db systems, but I'm fairly certain that many of the db queries are using mysql specific syntaxes.

I guess this is where the pdo crap and abstraction or active record would come in, but that would be a nightmare to code.

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 4:20 am
by Qphoria
Not really sure to be honest.. most syntax is the same for basic queries in all dbs that I've used professionally (oracle, sybase, sql) but haven't tried opencart with other DBs to know for sure.

Re: FYI: Upcoming Changes in 1.5.0

Posted: Mon Jan 03, 2011 4:31 am
by Xsecrets
Qphoria wrote:Not really sure to be honest.. most syntax is the same for basic queries in all dbs that I've used professionally (oracle, sybase, sql) but haven't tried opencart with other DBs to know for sure.
true if all your queries are purely standard, It's been a long time since I looked into what all is part of the standard, since I pretty much only use mysql, but I'm pretty sure some of the stuff used heavily is not standard.