Slightly new query.. Here's the new query and results of an explain..
I'm thinking that some indexing would help.. However, I don't really
know where to start.
---
EXPLAIN
SELECT
pnr.ID ID_pnr,
pnr.reservationdatetime,
pnr.conf_number,
pnr.created_by,
I have the following query that is running VERY slowly. Anyone have
any suggestions?
---
SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number,
pnr.created_by, GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY
name_last DESC SEPARATOR 'br') names, (SELECT
On 5/29/06, Cory wrote:
I have the following query that is running VERY slowly. Anyone have
any suggestions?
---
SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number,
pnr.created_by, GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY
name_last DESC SEPARATOR 'br') names, (SELECT
SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number,
pnr.created_by,
GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last
DESC SEPARATOR 'br') names,
(SELECT SUM(pf.base_fare*(SELECT COUNT(1) FROM pnr_passengers pp
WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares
On 5/29/06, Cory Robin wrote:
SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number,
pnr.created_by,
GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY name_last
DESC SEPARATOR 'br') names,
(SELECT SUM(pf.base_fare*(SELECT COUNT(1) FROM pnr_passengers pp
WHERE
Cory,
One way to lose the duplicate queries is to assign the count per pnr.id
to a user var, and calculate via that value:
SELECT
pnr.ID AS ID_pnr,
pnr.reservationdatetime,
pnr.conf_number,
pnr.created_by,
( SELECT @pp_cnt := COUNT(1)
FROM pnr_passengers pp
WHERE pp.ID_pnr = pnr.ID
