I have a lot of entries in a database table, and I want to maximize the performance of an SQL search that uses "like" Is it true that for queries involving multiple colums, searches faster when you create a view from the database using, say, one criteria, and then running the search on the subset of your table that resides in the view? Thanks!
No.
acts_as_ferret acts_as_sphinx acts_as_solr
Let me clarify. I create a view based on an indexed column and then perform the "LIKE" search on the view.
Thomas Preymesser wrote:
I don't think creating a view will be faster... Try this if applicable, basic tehcnique at the bottom with < and > works as well with MySQL. http://thoughts.n79.org/2006/03/07/optimizing-an-sql-like-query/
Other sphinx rails plugins:
http://ts.freelancing-gods.com/
http://blog.evanweaver.com/files/doc/fauna/ultrasphinx/files/README.html
Sphinx is really great, it’s best used for mostly static sites (or you’ll have to use thinkingsphinx’ delta field to manage a delta index).
I just recently had horrid experiences with ferret (yes, even with the drb server running production) on several production machines, but other people use it with success.
I know someone who’s using acts_as_solr with great success, although you do have to live with a fairly big Java footprint and you might need to play with the horrendous xml configs a bit depending on your goals.
And let’s not forget acts_as_searchable, which uses hyper estraier. The plugin is a bit outdated, but still works very well for me. Hyper estraier uses very little memory and is blazingly fast in the app I use it in.
If I had to choose, I’d go for sphinx (and a lot of rails developers seem to agree with me) for mainly static data apps or hyper estraier for data intensive apps (people might disagree here, but it has been great for me).
Best regards
Peter De Berdt
Unindexed searches like the MySQL LIKE searches are very resource intensive and slow on lots of data. Views won’t help you here. Use a real fulltext indexer instead, that’s what they exist for.
Best regards
Peter De Berdt
That's true, we gained like 3000% performance after indexing a 4 GiByte-log-file-table-column (< 1sec after in comparison to 20-30 seconds of query time). Although it can take a long time to index, MySQL does a pretty good job in searching in the index afterwarts.
A view is only a “view” to a real database-table. For example you can allow specific users of your database not to be able to see all the columns of database-table - for this purpose you would create a view.
If you do a search like “LIKE ‘…%abc%’…” on a table, the complete table has to be scanned. There is no difference if it is a view - the complete table has to be scanned for the value.
You can increase the speed if you are doing a “…LIKE ‘abc%’…” on an indexed column.
-Thomas
