• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

MySQL: MyISAM or INNODB?

Shamil

Well-known member
#1
Recently, vBulletin suggested that we convert our tables and engines to INNODB, slating many performance improvements, such as: faster query times and fewer table-locking issues.

Now, INNODB doesn't support FULLTEXT searching, which means that an external searching mechanism needs to be used.

What do you recommend and what schema will XF be using?
 

Erik

Well-known member
#4
Can someone inform me about the differences? I know that INNODB supports transactions, which would be pretty much necessary if they ever develop something like IP.Nexus. :)
 

Kier

XenForo Developer
Staff member
#6
We use MyISAM, InnoDB and Memory table types, depending upon the requirements of each table.

Each table type has its own benefits. For example, MyISAM is held back by table-level locking on writes whereas InnoDB only has to lock the particular row that is being written. However, if you can avoid locking, MyISAM is hugely faster for data writes than InnoDB. So it's not as simple as saying that one particular table type is better than another.

Horses for courses and all that.
 
F

Floris

Guest
#9
Thanks for explaining that Kier. What is the BLOB?
Used for holding larger amounts of data. Binary Large Object.


BLOB (Binary Large Object) is a large collection of binary data stored in a database table. Different database servers handles BLOB differently. But there are some common characteristics like:
  • BLOB stores binary data, which requires no encoding schema. BLOB data is stored in units of bytes.
  • BLOB data is usually large. Many database servers offer very high maximum sizes like 4 GB.
  • BLOB data is usually not directly stored inside the table. It is stored in different storage areas and its reference address is stored inside the table.
JDBC offers an interface, java.sql.Blob, to allow JDBC driver to provide generic functionalities to handle BLOB data in the database.
BLOB data can also be handled as byte arrays: byte[].
BLOB data can also be handled as binary streams with the java.io.InputStream class.
The data type and definition was introduced to describe data not originally defined in traditional computer database systems
 

Jerry

Well-known member
#11
MyISAM. Faster, though may or may not make it to disk as it writes to memory and replies, then writes to disk later. InnoDB hits the disk before responding, so loose speed and get consistency. Or just get a SSD and use flashcache !
 

Deebs

Well-known member
#13
We use MyISAM, InnoDB and Memory table types, depending upon the requirements of each table.
Kier,

What about other table types which use transaction locking? Specifically PBXT. Also, for those admins on shared hosts without access to InnoDB are they out of luck ie. is transactional support a must requirement for xenForo?
 

Deebs

Well-known member
#14
I am hoping XtraDB becomes the norm myself...
I think that many of those patches in XtraDB find their way back into the mainstream InnoDB release. Personally I have moved away from MySQL and over to MariaDB which is managed by Monty. It has some lovely patches to increase performance is apart from 2 things or so is fully drop-in compatible with MySQL.
 

Erik

Well-known member
#15
Kier,

What about other table types which use transaction locking? Specifically PBXT. Also, for those admins on shared hosts without access to InnoDB are they out of luck ie. is transactional support a must requirement for xenForo?
Can't you change the table type using something like phpMyAdmin? I'm pretty sure most hosts support InnoDB - what does being on a shared host have to do with it? :)
 

Deebs

Well-known member
#16
Can't you change the table type using something like phpMyAdmin? I'm pretty sure most hosts support InnoDB - what does being on a shared host have to do with it? :)
I know how to convert to many different table types. Having used MySQL since the 3.xx series. My question was about the table types not how to change them. I just prefer PBXT over InnoDB.
 

Erik

Well-known member
#17
I know how to convert to many different table types. Having used MySQL since the 3.xx series. My question was about the table types not how to change them. I just prefer PBXT over InnoDB.
I was just wondering about this:
Deebs said:
Also, for those admins on shared hosts without access to InnoDB
I don't know how someone on a shared host wouldn't have access to InnoDB... can you explain? :)
 

Eric

Active member
#19
I don't think I've ever come across a shared host that doesn't support InnoDB. You never know though, I suppose. :) Personally I tend to have InnoDB disabled (especially on VPS', depending on RAM).