Does this case call for InnoDB or MyISAM?

views:

answers:

+4 Q:

Does this case call for InnoDB or MyISAM?

I'm doing a search on a table (few inner joins) which takes max 5 seconds to run (7.5 million rows). It's a MyISAM table and I'm not using full-text indexing on it as I found there to be no difference in speed when using MATCH AGAINST and a normal "like" statement in this case from what I can see.

I'm now "suffering" from locked tables and queries running for several minutes before they complete because of it.

Would it benefit me at all to try and switch the engine to InnoDB? Or does that only help if I need to insert or update rows... not just select them? This whole table-locking thing is busy grinding my balls...

+1 A:

InnoDB supports row-level locking instead of table-level locking... so that should alleviate your problem (although I'm not sure it will remove it entirely).

Your best bet would be to use a dedicated search system (like Sphinx, Lucene, or Solr)

webdestroya 2010-07-28 18:50:44

SolR is another alternative, built on Lucene

OMG Ponies 2010-07-28 18:51:40

@OMG Ponies - Good call. I updated my answer

webdestroya 2010-07-28 18:52:40

Thanks for the comments, I'll look into the alternatives, however, for right now my question was answered by Mchl, and with the help of your opinions I can invest some more time into full-text indexing again to do some benchmark tests between with/without.The client needs to go live in a couple of days and the previous developers didn't compensate for the immense growth in data

AcidRaZor 2010-07-28 20:49:20

The difference between row-level and table-level locking is only important for insert and update queries. If you're mostly do selects (so the inserts/updates do not happen too often to lock the table) the difference will not be all that much (even though in recent benchmarks InnoDB seems to be outperforming MyISAM).

Other ways you could think about is to reorganise your data structure, perhaps including additional lookup table with 'tags' or 'keywords'. Implementing more efficient full text engine as suggested by webdestroya.

Last but not least, I'm also surprised that you got similar results with FULL TEXT vs LIKE. This could happen if the fields you're searching are not really wide, in which case maybe a stndard B-TREE index with = search would be enough?

Mchl 2010-07-28 18:59:07

Thanks. What do you mean not really wide? Currently the only fields I search by are 4 varchar(255) fields, all containing things like publisher/book title/subject category etc.Would full-text REALLY help that much and does it work more or less the same as composite indexes (where by, for example, product_id+publication_date for order by improves the speed dramatically)?It could be that this is what was wrong with the db (I took over from another developer) and why I got similar (if not better) speed using LIKE instead of match against

AcidRaZor 2010-07-28 20:47:33

ansaurus

tags:

views:

answers:

Does this case call for InnoDB or MyISAM?

related questions