MySQL - Innodb, search

Question

I recently switched from myisam to innodb in one of my mysql tables. Before i was using MATCH(). Since innodb doesn't support match() i decided to use LIKE.

Now I have a problem. My table is 190,6 MiB big and have 234,083 rows. When I use LIKE my server goes slow.

I switched to innodb from myisam to stop locking the table.

What am I gonna do? Thanks!

Install and use a real seach engine like Lucene? – Zoredache Jan 26 '11 at 01:57 — Zoredache, Jan 26 '11 at 01:57

score 2 · Accepted Answer · answered Jan 26 '11 at 02:51

As you know, MyISAM tables don't fare too well on write-heavy situations. InnoDB are better there; but don't have full-text indexes (to use match()). That boils down to a few answers:

you can have the write-heavy table(s) on InnoDB and the full-text one (should be just one or two) on MyISAM
if they're the same; or if several tables have big texts, then factor those texts out to a single (MyISAM) table with just the text and key fields to JOIN to any other table(s).
if the texts themselves are the ones with heavy writes, bite the bullet and use a dedicated text search engine (Sphinx, Lucene, etc)

Thats a great idea. I am creating a MyISAM table and put the big texts in there and join. Awesome, huge thanks! =) — Erik, Jan 26 '11 at 03:52

score 0 · Answer 2 · answered Jan 26 '11 at 02:02

This is a question I'm dealing with as well. Someone pointed me to the Sphinx engine.

Here's a post about the performance on the MySQL forums

It hasn't been pressing so far, so I haven't tested it out yet. If you go this route, please let me know your findings on the performance.

MySQL - Innodb, search

2 Answers2