CODEWITHSUNDEEP
mysqlruby-on-railsdatabaseoptimization
byCoder
byCoder

Reputation: 9184

What db to chose, using rails. Large db

I'm using very large database, some of tables have more than 30.000.000 entries, now I'm using mysql, but for some search queries i must to wait for more than 1-2 minutes. Is it any way to improve speed? Also, isn't any other db, which will work more fast? Also, what to use in rails? now i using simple %like%, is it any other ways to search via some field?

Upvotes: 1

Views: 465

Answers (3)

Bart Jedrocha
Bart Jedrocha

Reputation: 11570

Using %LIKE% for full text searching with that many rows will not yield performance. I would highly recommend using something like Sphinx and the excellent ThinkingSphinx gem. It works with MySQL and Rails/ActiveRecord out-of-the-box and configuration is simple.

This guide describes how to install the Sphinx search daemon onto a particular platform.

This guide describes how to install and use the thinking-sphinx gem. (Note that Rails 2 and 3 have different install guides so make sure you follow the correct one).

After the install, you can define indexes on the columns of the model you're interested in searching through.

Ryan Bates did a Railscasts episode on full text searching with ThinkingSphinx which is a great way to start.

Happy coding!

Upvotes: 5

Neil McGuigan
Neil McGuigan

Reputation: 48246

If you want to stick with mysql, then go with myisam.

Come up with a strategy for creating indexes intelligently.

Pre-calculate complex results strategically.

Only return the data you need.

Denormalize parts of the db to incr perf.

Use many slaves to read from

Split up the db into many dbs

You will need to test a lot.

Upvotes: 1

ShawnDaGeek
ShawnDaGeek

Reputation: 4150

as per %like%

I ran into a similar situation where i was using LIKE wild cards for searching a large table, with 3, 4 minute times. I was able to solve some of the issue by indexing the column and using MATCH() This might not work in your case if you need accuracy.

refer to http://dev.mysql.com/doc/refman//5.5/en/fulltext-search.html

as a NOTE: i am using a combo of both search and match, you can see here http://anotherfeed.com/feedmap.php?search=facebook the first 50 results returns LIKE, the last 50 return MATCH and displays the php index score as by mysql. The first 50 are always more accurate, since i am only indexing titles and not full descriptions.

$query2="SELECT *, MATCH(pagename) AGAINST('".urldecode($_REQUEST['search'])."') AS score FROM anotherfeedv3.af_freefeed WHERE MATCH(pagename) AGAINST('".urldecode($_REQUEST['search'])."') LIMIT $start, $limit";

Upvotes: 1

Related Questions