CODEWITHSUNDEEP
mysqlperformanceoptimizationhaving-clause
Jordan Daigle
Jordan Daigle

Reputation: 455

WHERE vs HAVING calculated field with multiple IFs/LIKEs

If i'm not wrong when using having, the query is executed without any filters then the code go over all the result to apply the filter. But in this case I'm using multiple Like//If to calculate my order. I was wondering what would be better between:

SELECT Page.*, 

if  (`flags` LIKE CONCAT('%',?,'%'),

    if  (`title` LIKE CONCAT('%',?,'%'),
        5,
        3
    ),

    if  (`title` LIKE CONCAT('%',?,'%'),
        2,
        0
    ),
) as pageOrder

FROM Page

WHERE `flags` LIKE CONCAT('%',?,'%') OR `title` LIKE CONCAT('%',?,'%')

ORDER BY pageOrder DESC

or 

SELECT Page.*, 

if  (`flags` LIKE CONCAT('%',?,'%'),

    if  (`title` LIKE CONCAT('%',?,'%'),
        5,
        3
    ),

    if  (`title` LIKE CONCAT('%',?,'%'),
        2,
        0
    ),
) as pageOrder

FROM Page

HAVING pageOrder > 0

ORDER BY pageOrder DESC

using mysql 5.7 and php 7.1

Upvotes: 0

Views: 53

Answers (1)

Rick James
Rick James

Reputation: 142366

In this case, there is not much difference. Here's an analysis.

  • Since no index can be used for either the WHERE or ORDER BY, the entire table will be scanned (for either formulation).

  • Expressions are usually a small part of the entire query, so usually I would ignore the LIKEs. But, in this case, you are doing 3-4 LIKEs for every row, about twice as many LIKEs for the WHERE approach, so it might be somewhat slower.

  • One could argue that the WHERE is more readable.

  • All the columns, most of the rows, of Page will be shoveled into a tmp table. But I don't see a big difference between the two approaches. Also, I don't necessarily see a way to avoid the issue.

Here is a slightly more compact, but not faster, way to formulate the pageOrder expression:

  3 * (`flags` LIKE CONCAT('%',?,'%') +
  2 * (`title` LIKE CONCAT('%',?,'%')

Upvotes: 1

Related Questions