I''ve heard that using LIKE is very slow, but I see it being used a lot in examples, blogs etc. Is it really that bad? Since Rails doesn''t directly support Fulltext search, this is the easiest way to get searching done, right? Or are there any other easier ways? I''m using acts_as_indexed right now, but it still doesn''t do what LIKE does. --~--~---------~--~----~------------~-------~--~----~ 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 Fri, Mar 13, 2009 at 12:42 PM, Mike C <snibble-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > I''ve heard that using LIKE is very slow, but I see it being used a lot > in examples, blogs etc. Is it really that bad? Since Rails doesn''t > directly support Fulltext search, this is the easiest way to get > searching done, right? Or are there any other easier ways? I''m using > acts_as_indexed right now, but it still doesn''t do what LIKE does.The problem with the sql "LIKE" is that it can render the use of an index impossible. Imagine a search like: @users = User.find(:all, :conditions => "first_name like ''%ILL%''") The database will have to find BILL, GILL, JILL, JILLIAN, etc. If you have a few hundred or a few thousand users that may not be such an issue. Millions of users makes this a different issue altogether. A full table scan is needed, and what will you do with all those rows? The point is this, when you are tempted to use LIKE be sure the data set searched and returned are small and that you try to other ways to constrain the search. Cheers-- Charles --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Thanks, that makes things clearer. But how big is big? I don''t plan on my app being hugely popular, but would thousands of entries be ok? Is there an area where LIKE starts to be really slow? On Mar 13, 11:06 am, Charles Johnson <gm.johns...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Fri, Mar 13, 2009 at 12:42 PM, Mike C <snib...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > I''ve heard that using LIKE is very slow, but I see it being used a lot > > in examples, blogs etc. Is it really that bad? Since Rails doesn''t > > directly support Fulltext search, this is the easiest way to get > > searching done, right? Or are there any other easier ways? I''m using > > acts_as_indexed right now, but it still doesn''t do what LIKE does. > > The problem with the sql "LIKE" is that it can render the use of an > index impossible. Imagine a search like: > > @users = User.find(:all, :conditions => "first_name like ''%ILL%''") > > The database will have to find BILL, GILL, JILL, JILLIAN, etc. If you have a > few hundred or a few thousand users that may not be such an issue. Millions > of users makes this a different issue altogether. A full table scan is > needed, and what will you do with all those rows? > > The point is this, when you are tempted to use LIKE be sure the data set > searched and returned are small and that you try to other ways to constrain > the search. > > Cheers-- > > Charles--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Likes are always slow if you must do a suffix match ( "%something" ), as no index can handle that. But if you''re doing a prefix match ( "something%" ) it won''t be that bad. And there''s no "how big is big". You can have a slow app with a thousand rows and a hundred queries per second, and you can have a fast one with a million rows and a query per second. It all depends on what you''re doing. Get a book on database optimization, all big databases have one, then you''ll undertand what''s fast and what''s slow about your database. - Maurício Linhares http://alinhavado.wordpress.com/ (pt-br) | http://blog.codevader.com/ (en) On Fri, Mar 13, 2009 at 3:11 PM, Mike C <snibble-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Thanks, that makes things clearer. But how big is big? I don''t plan on > my app being hugely popular, but would thousands of entries be ok? Is > there an area where LIKE starts to be really slow? > > On Mar 13, 11:06 am, Charles Johnson <gm.johns...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> On Fri, Mar 13, 2009 at 12:42 PM, Mike C <snib...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> >> > I''ve heard that using LIKE is very slow, but I see it being used a lot >> > in examples, blogs etc. Is it really that bad? Since Rails doesn''t >> > directly support Fulltext search, this is the easiest way to get >> > searching done, right? Or are there any other easier ways? I''m using >> > acts_as_indexed right now, but it still doesn''t do what LIKE does. >> >> The problem with the sql "LIKE" is that it can render the use of an >> index impossible. Imagine a search like: >> >> @users = User.find(:all, :conditions => "first_name like ''%ILL%''") >> >> The database will have to find BILL, GILL, JILL, JILLIAN, etc. If you have a >> few hundred or a few thousand users that may not be such an issue. Millions >> of users makes this a different issue altogether. A full table scan is >> needed, and what will you do with all those rows? >> >> The point is this, when you are tempted to use LIKE be sure the data set >> searched and returned are small and that you try to other ways to constrain >> the search. >> >> Cheers-- >> >> Charles > > >--~--~---------~--~----~------------~-------~--~----~ 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 Fri, Mar 13, 2009 at 1:11 PM, Mike C <snibble-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Thanks, that makes things clearer. But how big is big? I don''t plan on > my app being hugely popular, but would thousands of entries be ok? Is > there an area where LIKE starts to be really slow? >Depending on the speed of your hardware and the performance of your database I would look for other ways to search if you have more than a 1000 rows or two. I have a postgres database here with an accounts table with 1810 rows. A LIKE search for names like ''%ce% returned no rows in 25 ms. I have a jobs table with 5.3 million rows and the same search took 28 seconds to return no rows. Of course, if your database caches searches and you do the same search repetitively they will get faster and faster. I guess I would not worry about a few thousand rows. Get you app working, passing all tests, then worry about optimizations. Cheers-- Charles --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
> Thanks, that makes things clearer. But how big is big? I don''t plan on > my app being hugely popular, but would thousands of entries be ok? Is > there an area where LIKE starts to be really slow?If it''s a blog, I wouldn''t think twice about using LIKE. That is, I can''t see a blog being big enough for it to become a problem. However, as other folks have said it definitely can be the source of problems down the road. At that point you should look at one of the full text search engines like ferret, sphinx, solr, etc. Also, just so you''re aware LIKE in mysql is case-INSENSITIVE. LIKE in PostgreSQL is case-SENSITIVE. So when you''re searching for names and are happy doing a LIKE ''%ill%'' to find those names below, keep in mind that won''t work in PostgreSQL. PostgreSQL has an ILIKE for that, but then that''s not supported by MySQL. PostgreSQL also has some regex pattern matching functions that are faster, but again I believe are postgres only. Anyway, just something to keep in mind if you think your app might be run with different backends. -philip> On Mar 13, 11:06 am, Charles Johnson <gm.johns...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> On Fri, Mar 13, 2009 at 12:42 PM, Mike C <snib...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> >>> I''ve heard that using LIKE is very slow, but I see it being used a >>> lot >>> in examples, blogs etc. Is it really that bad? Since Rails doesn''t >>> directly support Fulltext search, this is the easiest way to get >>> searching done, right? Or are there any other easier ways? I''m using >>> acts_as_indexed right now, but it still doesn''t do what LIKE does. >> >> The problem with the sql "LIKE" is that it can render the use of an >> index impossible. Imagine a search like: >> >> @users = User.find(:all, :conditions => "first_name like ''%ILL%''") >> >> The database will have to find BILL, GILL, JILL, JILLIAN, etc. If >> you have a >> few hundred or a few thousand users that may not be such an issue. >> Millions >> of users makes this a different issue altogether. A full table scan >> is >> needed, and what will you do with all those rows? >> >> The point is this, when you are tempted to use LIKE be sure the >> data set >> searched and returned are small and that you try to other ways to >> constrain >> the search. >> >> Cheers-- >> >> Charles > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Thanks for the replies. Another thing I see are some search plugins that use LIKE in them predominantly and that''s also why I was wondering if it was ok. searchlogic was one I was looking at and so was search_do. On Mar 13, 11:59 am, Philip Hallstrom <phi...-LSG90OXdqQE@public.gmane.org> wrote:> > Thanks, that makes things clearer. But how big is big? I don''t plan on > > my app being hugely popular, but would thousands of entries be ok? Is > > there an area where LIKE starts to be really slow? > > If it''s a blog, I wouldn''t think twice about using LIKE. That is, I > can''t see a blog being big enough for it to become a problem. > However, as other folks have said it definitely can be the source of > problems down the road. > > At that point you should look at one of the full text search engines > like ferret, sphinx, solr, etc. > > Also, just so you''re aware LIKE in mysql is case-INSENSITIVE. LIKE in > PostgreSQL is case-SENSITIVE. > > So when you''re searching for names and are happy doing a LIKE ''%ill%'' > to find those names below, keep in mind that won''t work in > PostgreSQL. PostgreSQL has an ILIKE for that, but then that''s not > supported by MySQL. PostgreSQL also has some regex pattern matching > functions that are faster, but again I believe are postgres only. > > Anyway, just something to keep in mind if you think your app might be > run with different backends. > > -philip > > > On Mar 13, 11:06 am, Charles Johnson <gm.johns...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > >> On Fri, Mar 13, 2009 at 12:42 PM, Mike C <snib...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > >>> I''ve heard that using LIKE is very slow, but I see it being used a > >>> lot > >>> in examples, blogs etc. Is it really that bad? Since Rails doesn''t > >>> directly support Fulltext search, this is the easiest way to get > >>> searching done, right? Or are there any other easier ways? I''m using > >>> acts_as_indexed right now, but it still doesn''t do what LIKE does. > > >> The problem with the sql "LIKE" is that it can render the use of an > >> index impossible. Imagine a search like: > > >> @users = User.find(:all, :conditions => "first_name like ''%ILL%''") > > >> The database will have to find BILL, GILL, JILL, JILLIAN, etc. If > >> you have a > >> few hundred or a few thousand users that may not be such an issue. > >> Millions > >> of users makes this a different issue altogether. A full table scan > >> is > >> needed, and what will you do with all those rows? > > >> The point is this, when you are tempted to use LIKE be sure the > >> data set > >> searched and returned are small and that you try to other ways to > >> constrain > >> the search. > > >> Cheers-- > > >> Charles--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---