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! -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 05/02/2008, Cheri Ruska <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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 Preymesser thopre-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org thomas-/SbBnL9XF//QT0dZR+AlfA@public.gmane.org Büro: 030 - 830 353 88 mobil: 0176 - 75 03 03 04 Privat: 030 - 49 78 37 06 http://thopre.wordpress.com/ http://www.thopre.com/ --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
acts_as_ferret acts_as_sphinx acts_as_solr On Feb 5, 2008, at 12:40 PM, Cheri Ruska 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! > -- > Posted via http://www.ruby-forum.com/. > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Let me clarify. I create a view based on an indexed column and then perform the "LIKE" search on the view. Thomas Preymesser wrote:> On 05/02/2008, Cheri Ruska <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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 Preymesser > thopre-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org > thomas-/SbBnL9XF//QT0dZR+AlfA@public.gmane.org > Büro: 030 - 830 353 88 > mobil: 0176 - 75 03 03 04 > Privat: 030 - 49 78 37 06 > http://thopre.wordpress.com/ > http://www.thopre.com/-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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/ On 5 Feb., 22:43, Cheri Ruska <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> Let me clarify. I create a view based on an indexed column and then > perform the "LIKE" search on the view. > > > > Thomas Preymesser wrote: > > On 05/02/2008, Cheri Ruska <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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 Preymesser > > tho...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org > > tho...-/SbBnL9XF//QT0dZR+AlfA@public.gmane.org > > Büro: 030 - 830 353 88 > > mobil: 0176 - 75 03 03 04 > > Privat: 030 - 49 78 37 06 > >http://thopre.wordpress.com/ > >http://www.thopre.com/ > > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 05 Feb 2008, at 22:09, s.ross wrote:> acts_as_ferret > acts_as_sphinx > acts_as_solrOther 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. On Feb 6, 12:00 am, Peter De Berdt <peter.de.be...-LPO8gxj9N8aZIoH1IeqzKA@public.gmane.org> wrote:> 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--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 05/02/2008, Cheri Ruska <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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 Preymesser thopre-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org thomas-/SbBnL9XF//QT0dZR+AlfA@public.gmane.org Büro: 030 - 830 353 88 mobil: 0176 - 75 03 03 04 Privat: 030 - 49 78 37 06 http://thopre.wordpress.com/ http://www.thopre.com/ --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---