So, got a table with 4.6m rows of test data. Engine set to InnoDB, running a self-join query on it takes about a minute and a half. Create a copy of the table using MyISAM. Same query takes a tenth of a second. I mean, I know InnoDB is all transactional and referential integrity and shit, but that's fucked up.

 

Right. Just for larks, I'm going to see how it performs with 50m rows.

You rock, Tiger!

Got correct indexes on the columns you're joining with?

Generate the Query Execution Plan and see what it's trying to do (shove "EXPLAIN EXTENDED" in front of the select statement).

I think it's the COUNT(*) that's fucking it up.

I'm just bundling a PostgreSQL 8.4 instance that is almost certainly going to have the same problem.

From what I've read, MyISAM is the best bet for read-intensive applications, which this is. And most of the data operations are going to be inserts, with a small number of deletes and no updates at all. But I'll finish my testing anyway, because I am Diligent Man™

COUNT(*) takes ages for InnoDB tables as it actually counts the rows individually when you do it. MyISAM on the other hand keeps an internal counter, which means count(*) is fast, but introduces other problems of its own later on.

 

When you are volume testing these 2, make sure you try selects/inserts/updates concurrently as MyISAM can (does) have big scaling problems caused by the fact it uses table level locking in some fairly trivial situations, so while individual queries can seem fast in isolation when you introduce other operations you get deadlock hell.

 

If it helps, as a base rule, you should be using InnoDB unless you know you need MyISAM for some specific reason.

Interesting. The first hit from this query states "In Doubt? MyISAM".

Good for that link, in my 8 years experience of designing and eeking every bit of performance I can out of mysql databases (we are talking 2000 concurrent queries per second and millions of transactions per day) I have found the opposite to be the case.

 

In fact the only situation where I have seen myisam used in an "enterprise" system since approx. 2003 is where it is solely and knowingly a normalization of existing data, or for its fulltext index.

Only 8 years experience? I bet Truffy has many more years than that of searching Google, so I'm going to accept his answer as the correct one. :T

I wasn't doubting your advice, or experience. The DBs that I develop will /never/ reach the performance requirements that your work does. It's just a tad confusing when you suggest that MyISAM might be preferred for a specific reason, when the best that I can find is full-text indexing. That is all.

 

And Pete, shove it up your arse. Love'n'cuddles, ect.

Well, you just named one of the specific reasons there, so you have unconfused yourself, I hope.

But then there's the performance problem that Mark had right at the very start. If I have this correct, MyISAM would be useful for a DB-based site where SELECT queries are the norm, but InnoDB for transactional security and relational integrity (I usually fudge that in the DB design and script). I guess you can choose different engines for the same site, depending on whether there will be more/less SELECT queries.

 

From a personal POV, I could care less about full text indexing. Perhaps I shouldn't be so dismissive of it though.

No, you didn't read what I posted in reply to Mark originally.

 

myisam performance is not just down to the number of SELECTs you have, it's more complex than that and involves taking into account which of your queries will lock the entire table as they execute and which other queries may be running concurrently.

 

Any SELECTs involving joins, UPDATEs and INSERTs (in certain curcumstances) will cause the entire table to be write locked which may or may not be a problem depending on what else is going on at the same time.

 

For Marks tests, he seems to be doing quite intensive operations on a relatively large data set of 4.6m rows. I would hazard a guess that if he introduced some other query types on that data set to run concurrently (as one might get on a high transaction system) he would see some undesired locking situations.

you could care less or you could not care less? Which? You say could so I assume that's what you meant, but then your next sentence claims you're being dismissive so I begin to think it's the opposite.

Which queries does that "in certain circumstances" apply to - just INSERTs or all three?

(and what are the circumstances?)

I was thinking the same. So many people seem to get that statement wrong.

It's an american usage and as such is... fair enough. I suppose. It grates on me but if that's how they say it then that's how they say it.

But it doesn't make sense. Surely it's better to just be right and actually make sense. Fools.

I think it's just missing its second half, much like "great minds think alike...". It's also ore sarcastic I think.

MyISAM has a concurrent insert mode, which you can play with to allow it to append records to the end of the data file (rather than hunting for a gap in the middle of the data file) if there is another SELECT in progress, thus avoiding locking that SELECT out.

 

You can tailor it to your individual circumstances.

 

http://dev.mysql.com/doc/refman/5.1/en/concurrent-inserts.html

Is Truffy an American now? I'm so confused!

I mean, if I went around saying "well, that's the way the gryphon crumbles" would you be like "well, he is half Welsh, so it's fine"?