0

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!

Improve this question
1
  • Install and use a real seach engine like Lucene?
    – Zoredache
    Jan 26, 2011 at 1:57

2 Answers 2

Reset to default
2

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)

Improve this answer
1
  • 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, 2011 at 3:52
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .