slow query using year() and month()

[thompom]

hi,
have a slow running query which seems to be caused by using month and year on the where clause.
is there an alternative?

WHERE year(TOT.date) = year(current_date) and month(TOT.date) = month(current_date)

takes 8 secs with this WHERE clause/half a sec without!
will post full query if needed - has anyone else had this issue?

 

[DonQuichote]

Well, off course. If you compare the output of a function to a constant, the query can only be executed by executing that function for every row. Now if you used "greater and less" comparisons, the query would be really fast. Or even use a LIKE clause. Just try it.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[r937]

NOOOOOOOooooooooo....... never use LIKE with dates

use a date range, i.e. with greater and less comparisons

functions may be applied to a deterministic variable like CURRENT_DATE, as these expressions will be evaluate before execution commences against table rows (better yet, against an index)

WHERE TOT.date >= CURRENT_DATE
- INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
AND TOT.date < CURRENT_DATE
- INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
+ INTERVAL 1 MONTH

notice "greater than or equal" first day of current month at midnight, while only "less than" first day of following month at midnight

i.e. this works both for dates and for datetime values

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[thompom]

hi, thanks for the ideas but still v.slow - am going to look at indexes to help (never used them before)

 

[thompom]

...added an index to the date field and increased the query time by 2 secs!

 

[thompom]

...think the only solution to this may be to store the year and month of TOT.date in separate fields and run the query against these values rather than use mysql funcs

this system has gone live so need to get some queries that split out the month and year into new fields

something like

UPDATE table SET datemonth = month(TOT.date)

thanks to everyone for there help

 

[r937]

...think the only solution to this may be to store the year and month of TOT.date in separate fields

the only solution?


in a word... no

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[thompom]

hi rudy, had another go with this query and found that it wasnt slow just because of the WHERE line

if i can ask for your opinion again on the full query, it would be greatly appreciated

SELECT action.actiondate, cus.firstname, cus.surname, stock.stockstno, finprop.finco, acclink.accsum, stock.registrationnumber, stock.stockmake, stock.stockmod, action.username, stock.stockchasno, cus.postcode, financeco.financeco, action.actionid, site.site, saletype.saletype, dept.depttype, dept.deptbut, event.eventid, event.deptlinkid, date(TOT.date), IF((deal.del=1), 'Yes', 'No') AS dealdel, date(TOT2.date), event.userid, eventstocklink.statusid, fitoptlink.fitoptsum, eventstocklink.selling, eventstocklink.margin, eventstocklink.newtypeid, eventstocklink.packdisc, finprop.vb, finprop.warrcomm, finprop.fincomm, finprop.gap, deal.adminfee, finprop.sup, finprop.motins, finprop.other, stockstate.statedesc, discountlink.discountsum, eventstocklink.purch, eventstocklink.fda, eventstocklink.rqm, finprop.rate, finprop.agreetype, eventstocklink.qqm, eventstocklink.rom, eventstocklink.gm, eventstocklink.gmdeldisc, eventstocklink.gmoptdisc, eventstocklink.retailer, eventstocklink.retdeldisc, eventstocklink.retoptdisc, eventstocklink.associate, eventstocklink.assocdeldisc, eventstocklink.assocoptdisc, eventstocklink.pref, eventstocklink.regallow, eventstocklink.freeinsurance, eventstocklink.cussaving, eventstocklink.loyalty, eventstocklink.0finance, eventstocklink.listprice, eventstocklink.otrprice, eventstocklink.partnersid, eventstocklink.stocktypeid, bonuslink.bonussum, ST.saletype

FROM event 
LEFT JOIN action ON action.eventid = event.eventid 
LEFT JOIN saletypelink AS STL ON event.saletypeid = STL.saletypelinkid 
LEFT JOIN saletype AS ST ON STL.saletypeid = ST.saletypeid
LEFT JOIN response AS TOT ON TOT.actionid = action.actionid 

LEFT JOIN (SELECT R.date, R.actionid, TS.statusid, TA.eventid FROM action AS TA 
LEFT JOIN eventstocklink AS TS ON TS.eventstocklinkid = TA.eventstocklinkid LEFT JOIN stockstate AS TST ON TST.stateid = TS.statusid 
LEFT JOIN response AS R ON R.actionid = TA.actionid 
LEFT JOIN deal AS D ON D.actionid = TA.actionid 
WHERE D.del = 1 AND R.reasonid = 'Handover') 
AS TOT2 ON TOT2.eventid = event.eventid 

LEFT JOIN actiontype ON actiontype.actionid = action.typeid LEFT JOIN saletype ON saletype.saletypeid = STL.saletypeid LEFT JOIN eventstocklink ON eventstocklink.eventstocklinkid = action.eventstocklinkid 
LEFT JOIN stock ON stock.stockid = eventstocklink.stockid 
LEFT JOIN deptlink ON deptlink.deptlinkid = event.deptlinkid 
LEFT JOIN site ON site.siteid = deptlink.siteid 
LEFT JOIN dept ON dept.deptid = deptlink.deptid 
LEFT JOIN eventtype ON eventtype.eventid = event.typeid 
LEFT JOIN cus ON cus.cusid = event.cusid 
LEFT JOIN deal ON deal.actionid = action.actionid 
LEFT JOIN finprop ON finprop.eventstocklinkid = eventstocklink.eventstocklinkid 
LEFT JOIN financeco ON finprop.fincoid = financeco.finid LEFT JOIN stockstate ON stockstate.stateid = eventstocklink.statusid 

LEFT JOIN (SELECT accessorieslink.acclinkid, accessorieslink.actionid, sum(accessorieslink.accprice) AS accsum FROM accessorieslink GROUP BY accessorieslink.actionid) 
AS acclink ON acclink.actionid = action.actionid 

LEFT JOIN (SELECT fittedoptionslink.fitoptlinkid, fittedoptionslink.actionid, sum(fittedoptionslink.fitoptprice) AS fitoptsum FROM fittedoptionslink GROUP BY fittedoptionslink.actionid) 
AS fitoptlink ON fitoptlink.actionid = eventstocklink.eventstocklinkid 

LEFT JOIN (SELECT discountlink.discountlinkid, discountlink.actionid, sum(discountlink.discount) AS discountsum FROM discountlink GROUP BY discountlink.actionid) 
AS discountlink ON discountlink.actionid = action.actionid 

LEFT JOIN (SELECT bonuslink.bonuslinkid, bonuslink.actionid, sum(bonuslink.bonus) AS bonussum FROM bonuslink GROUP BY bonuslink.actionid) 
AS bonuslink ON bonuslink.actionid = eventstocklink.eventstocklinkid 

WHERE (action.typeid = '2' AND deal.lost = 0 AND event.saletypeid = 5 AND (eventstocklink.statusid = 2) AND year(TOT.date) = year(current_date) and month(TOT.date) = month(current_date) ) GROUP BY action.actionid

 

[ingresman]

And your suprised that is slow !
Not a helpful comment I know but wow !!

 

[r937]

i can't even read your query, never mind understand it

sorry, i know this is harsh, but you must learn to format your queries for readability

that wouldn't get me over the next hump, though, which is to understand all the various joins, which would require an understanding of all your various tables

however, let's make a start

let's take the very first join...

FROM event 
LEFT JOIN action 
ON action.eventid = event.eventid

the fact that you're using a LEFT JOIN here suggests that you want to retrieve all events, but some of these events don't have a matching action

if that's not true, and they actually all have actions, or, alternatively, if some events don't have actions but you're only interested in those that do, then you should be using INNER JOIN

a query with a series of LEFT OUTER JOINs will often run much slower than one using INNER JOINs

but as i said, i can't comment on that (and certainly not for all your joins) without understanding your tables and what they're for

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[thompom]

if anyones interested this problem was solved by moving
the TOT2 nested query to the bottom, just before the WHERE statement.
Further improvements were made by adding some INNER JOINS as rudy suggested.

LEFT JOIN (SELECT R.date, R.actionid, TA.eventid FROM action AS TA 
INNER JOIN response AS R ON R.actionid = TA.actionid 
INNER JOIN deal AS D ON D.actionid = TA.actionid 
WHERE D.del = 1 AND R.reasonid = 'Handover') 
AS TOT2 ON TOT2.eventid = event.eventid

thanks very much rudy

 

[ingresman]

I raise my hat to the MYSql query optimiser