in reply to Re^2: Querying 2 Values Form 1 Column in DBIx::Class
in thread Querying 2 Values Form 1 Column in DBIx::Class
SELECT * FROM People WHERE Name like '%Franklin%' and Name like '%Linsey%'Actually, I need AND. [...] Using OR would return a much larger result set than I was looking for. [...] So, AND helps narrow the search.
Just a thought: You might get the same result with OR by changing the search terms.
SELECT * FROM People WHERE Name LIKE '%Franklin%Linsey%' OR Name LIKE '%Linsey%Franklin%'
I have no idea what difference in performance this will get out of your database. I'm quite sure there will be a difference. Talk to your DB admin.
Another thing that I've learned from an Oracle guru is to avoid the LIKE operator where INSTR (other databases may call it POSITION or STRPOS) is sufficient. The reason behind that is that it is quite easy for an RDBMS to optimize (i.e. create an index) for a substring search, but LIKE most times requires a slow full table scan, operating on a search pattern. Again, talk to your DB admin.
SELECT * FROM People WHERE INSTR(Name,'Franklin')>0 AND INSTR(Name,'Linsey')>0
Alexander
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
|
---|
Replies are listed 'Best First'. | |
---|---|
Re^4: Querying 2 Values Form 1 Column in DBIx::Class
by erix (Prior) on Jan 28, 2018 at 00:27 UTC |