# Faster MySQL searches **URL:** \ **Category:** Rails\ **Created:** [February 5, 2008, 9:40pm UTC](https://www.ruby-forum.com/t/faster-mysql-searches/128209 "2008-02-05T21:40:07Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![liquidrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/a6a055/32.png) [@liquidrails](https://www.ruby-forum.com/u/liquidrails)\ **Post date:** [February 5, 2008, 9:40pm UTC](https://www.ruby-forum.com/t/faster-mysql-searches/128209/1 "2008-02-05T21:40:07Z")
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! ---
**Author:** ![liquidrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/a6a055/32.png) [@liquidrails](https://www.ruby-forum.com/u/liquidrails)\ **Post date:** [February 5, 2008, 9:57pm UTC](https://www.ruby-forum.com/t/faster-mysql-searches/128209/2 "2008-02-05T21:57:37Z")
On 05/02/2008, Cheri R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > 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. – Thomas P. removed\_email\_address@domain.invalid removed\_email\_address@domain.invalid Büro: 030 - 830 353 88 mobil: 0176 - 75 03 03 04 Privat: 030 - 49 78 37 06 > **[thopre](https://thopre.wordpress.com/)** > > Entwicklungen für die Bühne des Lebens [http://www.thopre.com/](http://www.thopre.com/) ---
**Author:** ![liquidrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/a6a055/32.png) [@liquidrails](https://www.ruby-forum.com/u/liquidrails)\ **Post date:** [February 5, 2008, 10:20pm UTC](https://www.ruby-forum.com/t/faster-mysql-searches/128209/3 "2008-02-05T22:20:44Z")
acts\_as\_ferret acts\_as\_sphinx acts\_as\_solr ---
**Author:** ![liquidrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/a6a055/32.png) [@liquidrails](https://www.ruby-forum.com/u/liquidrails)\ **Post date:** [February 5, 2008, 10:43pm UTC](https://www.ruby-forum.com/t/faster-mysql-searches/128209/4 "2008-02-05T22:43:27Z")
Let me clarify. I create a view based on an indexed column and then perform the “LIKE” search on the view. Thomas P. wrote: > On 05/02/2008, Cheri R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > 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. > > – > Thomas P. > removed\_email\_address@domain.invalid > removed\_email\_address@domain.invalid > Büro: 030 - 830 353 88 > mobil: 0176 - 75 03 03 04 > Privat: 030 - 49 78 37 06 > [http://thopre.wordpress.com/](http://thopre.wordpress.com/) > [http://www.thopre.com/](http://www.thopre.com/) ---
**Author:** ![liquidrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/a6a055/32.png) [@liquidrails](https://www.ruby-forum.com/u/liquidrails)\ **Post date:** [February 5, 2008, 11:59pm UTC](https://www.ruby-forum.com/t/faster-mysql-searches/128209/5 "2008-02-05T23:59:46Z")
On 05 Feb 2008, at 22:09, s.ross wrote: > acts\_as\_ferret > acts\_as\_sphinx > acts\_as\_solr Other sphinx rails plugins: > **[Home § Thinking Sphinx](https://freelancing-gods.com/thinking-sphinx/)** > > Thinking Sphinx: A Rails and Merb plugin for Sphinx [http://blog.evanweaver.com/files/doc/fauna/ultrasphinx/files/README.html](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 ---
**Author:** ![liquidrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/a6a055/32.png) [@liquidrails](https://www.ruby-forum.com/u/liquidrails)\ **Post date:** [February 6, 2008, 12:10am UTC](https://www.ruby-forum.com/t/faster-mysql-searches/128209/6 "2008-02-06T00:10:23Z")
On 05 Feb 2008, at 23:38, Michael Whittaker wrote: > > > > 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? 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 ---
**Author:** ![liquidrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/a6a055/32.png) [@liquidrails](https://www.ruby-forum.com/u/liquidrails)\ **Post date:** [February 5, 2008, 11:39pm UTC](https://www.ruby-forum.com/t/faster-mysql-searches/128209/7 "2008-02-05T23:39:09Z")
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/](http://thoughts.n79.org/2006/03/07/optimizing-an-sql-like-query/) On 5 Feb., 22:43, Cheri R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![liquidrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/a6a055/32.png) [@liquidrails](https://www.ruby-forum.com/u/liquidrails)\ **Post date:** [February 6, 2008, 7:05pm UTC](https://www.ruby-forum.com/t/faster-mysql-searches/128209/8 "2008-02-06T19:05:46Z")
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. ---
**Author:** ![liquidrails](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/l/a6a055/32.png) [@liquidrails](https://www.ruby-forum.com/u/liquidrails)\ **Post date:** [February 6, 2008, 7:45pm UTC](https://www.ruby-forum.com/t/faster-mysql-searches/128209/9 "2008-02-06T19:45:16Z")
On 05/02/2008, Cheri R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Let me clarify. I create a view based on an indexed column and then > perform the “LIKE” search on the view. 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 – Thomas P. removed\_email\_address@domain.invalid removed\_email\_address@domain.invalid Büro: 030 - 830 353 88 mobil: 0176 - 75 03 03 04 Privat: 030 - 49 78 37 06 > **[thopre](https://thopre.wordpress.com/)** > > Entwicklungen für die Bühne des Lebens [http://www.thopre.com/](http://www.thopre.com/)