1

The query below takes ~2 seconds, which seems really long for such a straightforward join on 2 tables with only ~3000 or so rows.

I strongly suspect the problem is with this line:

but I'm not sure why. Or, perhaps the join condition is screwing things up?

SELECT DISTINCT
, idnum
, DATEDIFF( DATE_ADD(atable.adate, INTERVAL 10 DAY), btable.bdate) as `DIFF`
  FROM atable
LEFT JOIN btable
ON atable.idnum = btable.idnum
;

My issue is similar to this, but not the same. Thanks in advance to the great SO community for looking this over.

Community
  • 1
  • 1
Monica Heddneck
  • 2,973
  • 10
  • 55
  • 89
  • 1
    The most likely explanation is the absence of suitable indexes. And the DISTINCT keyword is going to force MySQL to perform a "Using filesort" operation to identify and remove duplicates. Use EXPLAIN to see the execution plan. And be sure that btable has an index with leading column of idnum. Ideally, covering indexes for both tables.... ON btable (idnum,bdate) and ON atable (idnum,adate). I suspect that the performance impact of the DATEDIFF and DATE_ADD functions is negligible. I suspect the tall pole is materializing the result and the "Using filesort" operation. – spencer7593 Mar 21 '17 at 04:40
  • Thanks for your help. EXPLAIN told me it was an 'all' type join. You are correct -- commenting out the DATEDIFF part didn't speed up the query at all! – Monica Heddneck Mar 21 '17 at 04:43
  • Without a WHERE clause (any predicate on atable), we would expect the EXPLAIN to show "all"... the query has to access every row in atable. What we want to know is the access plan for the outer join to `btable`. Ideally, with a covering index, we would see "Using index" in the Extra column. What shows up in the `type` column is going to depend on the uniqueness and nullability of `idnum` in btable, and the availability of a suitable index. . – spencer7593 Mar 21 '17 at 04:49
  • Please provide `SHOW CREATE TABLE` for both tables. – Rick James Mar 22 '17 at 00:57
  • Expressions in `SELECT` have relatively little to do with slowness. So, I declare that DATEADD and DATEDIFF are not the villains. – Rick James Mar 22 '17 at 00:59

1 Answers1

0

You can reduce time by creating view of this query and call that view rather than direct query to table.like this

Create view

CREATE VIEW viewname AS SELECT DISTINCT
, idnum
, DATEDIFF( DATE_ADD(atable.adate, INTERVAL 10 DAY), btable.bdate) as `DIFF`
  FROM atable
LEFT JOIN btable
ON atable.idnum = btable.idnum
;

Call view

SELECT * FROM viewname

Note: in view approach we create view at once and call that view as many times as we want.

Varun Malhotra
  • 1,202
  • 3
  • 15
  • 28
  • A good demonstration of creating a stored view and referencing it in a query. But how does this "reduce time"? Is there any evidence that referencing a view will actually improve performance i.e reduce query execution time? (Note: for analyzing performance, we want to bypass the mysql query cache,) – spencer7593 Mar 21 '17 at 04:56
  • Technically views are the temp tables which acts like cache it is a pre built data which react instantly to the request.but in direct query sql engine first create the temp data and then responds. so in other words we save the time of temp data generation on the fly by using view. :) – Varun Malhotra Mar 21 '17 at 05:01
  • And here is the proof http://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query – Varun Malhotra Mar 21 '17 at 05:04
  • The "proof" is null and void because it applies to sql-server, not MySQL. – Rick James Mar 22 '17 at 00:54
  • mysql is also based in the sql server and used the dbengines for every thing happning in it. – Varun Malhotra Mar 22 '17 at 03:51