0

I have table journal containing these columns: 

| id | journalName | issn |

I need to: filter the table based on the concatenation result of journalName & issn (using LIKE syntax) - and display only the journalName and issn (concatenation result are not selected). Concatenation result need to filtered against several LIKE criteria, so it would be reasonable to put them inside a variable.

This is WHERE approach (concat is called repeatedly):

SELECT journalName, issn
FROM `journal`
WHERE 
CONCAT_WS(' ',journalName,issn) LIKE '%375%' AND
CONCAT_WS(' ',journalName,issn) LIKE '%res%'

My current approach (use variable but two SELECT statements):

SELECT journalName, issn FROM
(
    SELECT journalName, issn, CONCAT_WS(' ',journalName,issn) AS searchable
    FROM `journal`
    HAVING 
    searchable LIKE '%375%' AND
    searchable LIKE '%res%'
    ORDER BY searchable DESC
) t

Is there any other way to do it using only one SELECT statement while having concat result stored in a variable simultaneously?

n.b.: Adding column is not an option. I have read this and this

Community
  • 1
  • 1
Luke
  • 153
  • 2
  • 8
  • The first potential improvement would be to change the HAVING part of the second option to be `HAVING searchable LIKE '%375%res%' OR searchable LIKE '%res%375%'`. This would reduce the number of checks by 50% (assuming that there are 50% chances that _res_ is after _375_; if you know that the order will be one of the values always first, you can reduce it to a single option). A similar approach can be used in the first example. – FDavidov Dec 05 '16 at 07:49
  • You will not see a difference between your approaches, heck, you probably won't even see a measurable difference when you remove one of your conditions completely (assuming you get the same amount of rows). And actually you could use `where (journalName like '%375%' or issn like '%375%') and (journalName like '%res%' or issn like '%res%') order by journalName desc, issn desc`. You can even use an index on `(journalName, issn)` then. It depends on your table and how many rows you get if it speeds it up, but at least this approach actually has a chance to have a measurable impact. – Solarflare Dec 05 '16 at 12:30

0 Answers0