Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
Install and use a real seach engine like Lucene? –  Zoredache Jan 26 '11 at 1:57
add comment

2 Answers

up vote 2 down vote accepted

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)

share|improve this answer
    
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 3:52
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.