Mysql - Add if condition in the Mysql Query or a where clause

Question

I have written a join query in MySQL which works well and shows the result.

I am trying to write a MySQL query that shows 2 additional columns with some calculations

 If isPercent=1 then 
New Column1=price*currentPercent/100 
New Column2=LineItemQuantity*price

I tried to write this query in PHP but since there are 100,000s records it is timing out.

Here is MySQL query and the results shown below

Select 
wl.LineItems_LineItemID,
wl.LineItemQuantity, 
pj.IsPercent, 
pj.CurrentPercent,
pj.CurrentRate,
cb.Price 
from 
WorkOrderLineItems wl, 
PayScaleLoaclJObCodes pj, 
ClientBillingRates cb 
where 
wl.LineItems_LineItemID=pj.JobCodeID 
AND wl.LineItems_LineItemID=cb.ClientBillingRates_ID 
AND pj.PayScalesLocal_ID='33'

You could totally try to specify explicit joins instead of doing them implicitly thru WHERE conditions. — Alfabravo, Mar 12 '18 at 20:41
@Alfabravo Technically speaking, isn't implicit join using WHERE the same as explicit JOIN statements? There should be no performance difference: https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins — Terry, Mar 12 '18 at 20:47
@user580950 Add `EXPLAIN EXTENDED` to the front of your SQL statement. What kind of indeces is the query using? I am suspecting that your tables are not properly indexed, causing the request to timeout. A hint: create indexes that are used in your WHERE clause. — Terry, Mar 12 '18 at 20:47
@Terry, Yes. Old SQL-89 style joins are compatible with modern `JOIN` syntax, and have the same performance. But you can't do `OUTER JOIN` with old style syntax. It's worthwhile to adapt to the "modern" syntax that has been standard since 1992! — Bill Karwin, Mar 12 '18 at 20:52
@Terry Still, order of statements in WHERE might affect the execution time depending on DB engine. Best way to know is to EXPLAIN it and compare. :) Edit to say that the same question you quote has that same idea in an answer. Last: maybe specifying a kind of explicit join could reduce the execution time while getting the desired resultset. — Alfabravo, Mar 12 '18 at 20:55

score 1 · Accepted Answer · answered Mar 12 '18 at 20:59

I would write the query this way:

SELECT 
  wl.LineItems_LineItemID,
  wl.LineItemQuantity, 
  pj.IsPercent, 
  pj.CurrentPercent,
  pj.CurrentRate,
  cb.Price,
  IF(pj.IsPercent=1, cb.Price*pj.CurrentPercent/100, NULL) AS `New Column 1`,
  IF(pj.IsPercent=1, wl.LineItemQuantity*cb.Price, NULL) AS `New Column 2`
FROM 
WorkOrderLineItems wl
JOIN PayScaleLoaclJObCodes pj ON wl.LineItems_LineItemID = pj.JobCodeID 
JOIN ClientBillingRates cb ON wl.LineItems_LineItemID = cb.ClientBillingRates_ID 
WHERE pj.PayScalesLocal_ID = '33'

As in the comments above, I encourage you to use JOIN syntax instead of relying on old-fashioned comma-style joins.

As for the query timing out, I would guess that you don't have the right indexes to support this query. If you want help with query optimization, you should run SHOW CREATE TABLE <tablename> for each table in your query, and post the output in your question.

Mysql - Add if condition in the Mysql Query or a where clause

1 Answers1