Re: Question about LIMIT

[Dmitriy Reznik]

Thank you very much for your help. I started eliminating left outer joins as you suggested, and even without removing GROUP BY b.BookStem it became several times faster. But I still have problems with restructuring this part:
MAX(CASE WHEN r.r_type = 'Print' THEN r.Excerpts END) PrintReviews,
(CASE WHEN MAX(CASE WHEN rr.Award LIKE 'PW%' THEN 1 ELSE 0 END) = 1 THEN 'X' ELSE '' END) AS PW,
(CASE WHEN MAX(CASE WHEN rr.Award LIKE 'BookList%' THEN 1 ELSE 0 END) = 1 THEN 'X' ELSE '' END) AS BL,
(CASE WHEN MAX(CASE WHEN rr.Award LIKE 'Library Journal%' THEN 1 ELSE 0 END) = 1 THEN 'X' ELSE '' END) AS LJ,
(CASE WHEN MAX(CASE WHEN rr.Award LIKE 'BookPage%' THEN 1 ELSE 0 END) = 1 THEN 'X' ELSE '' END) AS BP,
...
FROM BookList b
LEFT OUTER JOIN
(SELECT BookStem, r_type, GROUP_CONCAT(DISTINCT Excerpt SEPARATOR '<br />') Excerpts
FROM Reviews
GROUP BY BookStem, r_type) r
ON b.BookStem = r.BookStem
LEFT OUTER JOIN Reviews rr ON b.BookStem = rr.BookStem

Could you please help me with this?
The whole query now looks like this:
SELECT b.Title,
(SELECT GROUP_CONCAT(DISTINCT CONCAT(a.LastName, ', ', a.FirstName) SEPARATOR '<br />') FROM AuthorLinks al JOIN Authors a ON al.AuthorStem = a.AuthorStem WHERE b.BookStem = al.BookStem) AS Author,
(SELECT GROUP_CONCAT(DISTINCT CONCAT(n.LastName, ', ', n.FirstName) SEPARATOR '<br />') FROM NarratorLinks nl JOIN Narrators n ON nl.NarratorStem = n.NarratorStem WHERE b.BookStem = nl.BookStem) AS Narrator,
(SELECT GROUP_CONCAT(DISTINCT CONCAT(bc.Subject) SEPARATOR '<br />') FROM BisacLinks bl JOIN BisacCodes bc ON bl.BisacCode = bc.BisacCode WHERE b.BookStem = bl.BookStem) AS Genre,
b.PublicationDate, b.Imprint, b.1stPrint as FirstPrint,
CASE WHEN b.OrigAudPubDate IS NULL OR b.HCDate IS NULL THEN NULL
WHEN b.OrigAudPubDate <= b.HCDate OR
(b.OrigAudPubDate > b.HCDate AND b.OrigAudPubDate < DATE_ADD(b.HCDate, INTERVAL 3 MONTH))
THEN 'front'
WHEN b.OrigAudPubDate > DATE_ADD(b.HCDate, INTERVAL 3 MONTH)
THEN 'back'
END AS FrontlistBacklist,
(SELECT GROUP_CONCAT(DISTINCT CONCAT(ar.Name, ': ', ar.QltyReview) SEPARATOR '<br />') FROM AudioReviews ar WHERE b.BookStem = ar.BookStem) AS QltyReview,
MAX(CASE WHEN r.r_type = 'Print' THEN r.Excerpts END) PrintReviews,
(CASE WHEN MAX(CASE WHEN rr.Award LIKE 'PW%' THEN 1 ELSE 0 END) = 1 THEN 'X' ELSE '' END) AS PW,
(CASE WHEN MAX(CASE WHEN rr.Award LIKE 'BookList%' THEN 1 ELSE 0 END) = 1 THEN 'X' ELSE '' END) AS BL,
(CASE WHEN MAX(CASE WHEN rr.Award LIKE 'Library Journal%' THEN 1 ELSE 0 END) = 1 THEN 'X' ELSE '' END) AS LJ,
(CASE WHEN MAX(CASE WHEN rr.Award LIKE 'BookPage%' THEN 1 ELSE 0 END) = 1 THEN 'X' ELSE '' END) AS BP,
b.DL_Rank, b.HC_Rank, b.BookStem
FROM BookList b
-- LEFT OUTER JOIN (AuthorLinks al JOIN Authors a ON al.AuthorStem = a.AuthorStem)
-- ON b.BookStem = al.BookStem
-- LEFT OUTER JOIN (NarratorLinks nl JOIN Narrators n ON nl.NarratorStem = n.NarratorStem)
-- ON b.BookStem = nl.BookStem
-- LEFT OUTER JOIN (BisacLinks bl JOIN BisacCodes bc ON bl.BisacCode = bc.BisacCode)
-- ON b.BookStem = bl.BookStem
-- LEFT OUTER JOIN AudioReviews ar
-- ON b.BookStem = ar.BookStem
LEFT OUTER JOIN
(SELECT BookStem, r_type, GROUP_CONCAT(DISTINCT Excerpt SEPARATOR '<br />') Excerpts
FROM Reviews
GROUP BY BookStem, r_type) r
ON b.BookStem = r.BookStem
LEFT OUTER JOIN Reviews rr ON b.BookStem = rr.BookStem
GROUP BY b.BookStem ORDER BY b.BookStem -- limit 0, 100