CODEWITHSUNDEEP
sqlmysqlquery-optimization
Cheng
Cheng

Reputation: 4886

How to eliminate duplicate calculation in SQL?

I have a SQL that can be simplified to:

SELECT * 
  FROM table 
 WHERE LOCATE( column, :keyword ) > 0 
ORDER BY LOCATE( column, :keyword )

You can see there is a duplicate of "LOCATE( column, :keyword )". Is there a way to calculate it only once ?

Upvotes: 4

Views: 574

Answers (3)

six8
six8

Reputation: 2990

HAVING works with aliases in MySQL:

SELECT *, LOCATE( column, :keyword ) AS somelabel 
FROM table 
HAVING somelabel > 0 
ORDER BY somelabel

Upvotes: 2

JosephStyons
JosephStyons

Reputation: 58685

Jeff Ober has the right idea, but here is an alternative method:

SELECT
  t.*
 ,loc.LOCATED
FROM
  table t
  INNER JOIN
  (
  SELECT
    primary_key
   ,LOCATE(column,:keyword) AS LOCATED
  FROM
    table 
  ) loc
  ON t.primary_key = loc.primary_key
WHERE loc.LOCATED > 0
ORDER BY
  loc.LOCATED

Upvotes: 2

Jeff Ober
Jeff Ober

Reputation: 5027

SELECT *, LOCATE( column, :keyword ) AS somelabel 
FROM table 
WHERE somelabel > 0 
ORDER BY somelabel

Upvotes: 5

Related Questions