0

RE these two answers to an old question: Answer 1, Answer 2.

I have a query which selects 14 columns and uses 10 LEFT JOINs, which I've attempted to simplify below. I'm hoping to be able to return the total results COUNT (irrespective of LIMIT/OFFSET) as part of the same query.

My question: is there a dramatic performance difference depending on which of the two answers I employ or are they pretty much doing the same thing?

e.g. Answer 1

SELECT
a.id,
b.name AS course,
DATE_FORMAT(a.start_date,'%d %M %Y') AS start_date,
DATE_FORMAT(a.end_date,'%d %M %Y') AS end_date,
/* MANY MORE FIELDS IN SELECT STATEMENT */,
(SELECT COUNT(*) AS count FROM courses AS a /* LEFT JOINs & WHEREs */) AS count,
FROM courses AS a
/* MANY LEFT JOINS */
WHERE
a.start_date > CURRENT_DATE(),
/* A FEW MORE WHERE STATEMENTS */
LIMIT 5 OFFSET 15

or Answer 2

SELECT
a.id,
b.name AS course,
DATE_FORMAT(a.start_date,'%d %M %Y') AS start_date,
DATE_FORMAT(a.end_date,'%d %M %Y') AS end_date,
/* MANY MORE FIELDS IN SELECT STATEMENT */,
z.count
FROM courses AS a
/* MANY LEFT JOINS */,
(SELECT COUNT(*) AS count FROM courses AS a /* LEFT JOINs/WHEREs */) AS z
WHERE
a.start_date > CURRENT_DATE(),
/* A FEW MORE WHERE STATEMENTS */
LIMIT 5 OFFSET 15

Right now with a handful of test records there's no difference but I wonder if there's a simple YES/NO for when we're returning 100s or 1000s of records?

Community
  • 1
  • 1
PedroClemo
  • 23
  • 1
  • 7
  • 1
    You should test with a larger data set, but also try using `EXPLAIN` to see if any notable differences stand out. I think an exact answer would be difficult based only on the information you gave. – Tim Biegeleisen May 16 '17 at 11:58
  • Possible duplicate of [Performance of inner join compared to cross join](http://stackoverflow.com/questions/670980/performance-of-inner-join-compared-to-cross-join) – Alexander May 16 '17 at 12:00

0 Answers0