Results 1 to 9 of 9

Thread: Slow left join

  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Slow left join

    This query works the goal is to show all from CBA and the Dues_YEAR from DUES _PAID which has multiple entries of the CBA_ID. I t returns the right records but it took a couple of minutes!
    Nick


    SELECT c.CBA_ID
    , c.LNAME
    , c.FN_MI
    , MAX(DUES_YEAR)
    FROM CBA AS c
    LEFT OUTER
    JOIN DUES_PAID AS d
    ON d.CBA_ID = c.CBA_ID
    GROUP BY d.CBA_ID

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please do a SHOW CREATE TABLE for each of your tables

    i wants to see da indexes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Also, that query won't run because LNAME and FN_MI need to be in the GROUP BY clause, or contained within aggregate expressions... Can you provide the full query?
    George
    Home | Blog

  4. #4
    Join Date
    Dec 2008
    Posts
    135
    i think it will give u the error as
    because it is not contained in either an aggregate function or the GROUP BY clause.
    columns doesn't contain in group clause

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Also, that query won't run because LNAME and FN_MI need to be in the GROUP BY clause
    no they don't

    this is mysql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    D'uh, of course
    It will run, but the results might not make any sense
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in general, they might not, but in this particular case, they obviously will!!

    see Debunking GROUP BY myths and the discussion of functional dependence
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2009
    Posts
    124
    Sorry but this is what I inherited!

    CREATE TABLE `CBA` (
    `CBA_ID` int(10) NOT NULL auto_increment,
    `access_usr` int(11) unsigned default NULL,
    `randomkey_usr` varchar(100) default NULL,
    `active_usr` int(25) unsigned default NULL,
    `black_balled` varchar(4) default NULL,
    `username_usr` varchar(100) default NULL,
    `password_usr` tinyint(100) default NULL,
    `committee_usr` varchar(100) default NULL,
    `s_Generation` int(10) default NULL,
    `ID` int(10) default NULL,
    `s_GUID` varchar(36) default NULL,
    `LNAME` varchar(28) default NULL,
    `s_Lineage` blob,
    `SN` smallint(5) default NULL,
    `FN_MI` varchar(22) default NULL,
    `TITLE` varchar(9) default NULL,
    `SX` varchar(10) default NULL,
    `DJ` varchar(5) default NULL,
    `FN_SP` varchar(30) default NULL,
    `SPNAME` varchar(30) default NULL,
    `SPLNAME` varchar(50) default NULL COMMENT 'Spouse''s Last Name - added 1Oct05',
    `ADD1` varchar(50) default NULL,
    `ADD2` varchar(50) default NULL,
    `City` varchar(30) default NULL,
    `ST` char(2) default NULL,
    `ZIP` varchar(18) default NULL,
    `ADD3` varchar(30) default NULL,
    `ADD4` varchar(255) default NULL,
    `CITY2` varchar(255) default NULL,
    `ST2` varchar(255) default NULL,
    `ZIP2` varchar(255) default NULL,
    `country` varchar(100) default NULL,
    `PHONE1` varchar(255) default NULL,
    `PHONE2` varchar(255) default NULL,
    `SEARCH_KEY` varchar(255) default NULL,
    `EMail` varchar(255) default NULL,
    `PR_LABEL` varchar(255) default NULL,
    `INFO` varchar(255) default NULL,
    `ROLE` varchar(255) default NULL,
    `NOTE1` varchar(255) default NULL,
    `NOTE2` varchar(255) default NULL,
    `NOTE3` varchar(50) default NULL,
    `OWNER_1ST` varchar(255) default NULL,
    `2_2` varchar(255) default NULL,
    `BN1` varchar(255) default NULL,
    `SB1` varchar(255) default NULL,
    `PORT1` varchar(255) default NULL,
    `XNAME1` varchar(255) default NULL,
    `FO1` varchar(255) default NULL,
    `DS1` varchar(255) default NULL,
    `YR1` varchar(255) default NULL,
    `BLDTYP1` varchar(255) default NULL,
    `BLDLOC1` varchar(255) default NULL,
    `L1` varchar(255) default NULL,
    `B1` varchar(255) default NULL,
    `SA1` varchar(255) default NULL,
    `DD1` varchar(255) default NULL,
    `1_1` int(10) default NULL,
    `HR1` varchar(255) default NULL,
    `SOLD1` varchar(50) default NULL,
    `UPDATED` datetime default NULL,
    `cdate` datetime default NULL,
    `edate` date default NULL,
    `PRINT` varchar(255) default NULL,
    `DUESPD99` varchar(255) default NULL,
    `DUESPD98` datetime default NULL,
    `DUESPD97` varchar(4) default NULL,
    `DUESPD96` varchar(4) default NULL,
    `DUESPD95` varchar(255) default NULL,
    `MBR_DIED` varchar(255) default NULL,
    `SP_DIED` varchar(50) default NULL,
    `BN2` varchar(255) default NULL,
    `PORT2` varchar(255) default NULL,
    `XNAME2` varchar(255) default NULL,
    `FO2` varchar(255) default NULL,
    `DS2` varchar(255) default NULL,
    `SB2` varchar(255) default NULL,
    `YR2` varchar(255) default NULL,
    `BTYP2` varchar(255) default NULL,
    `BLDLOC2` varchar(255) default NULL,
    `L2` varchar(255) default NULL,
    `B2` varchar(255) default NULL,
    `SA2` varchar(255) default NULL,
    `DD2` varchar(255) default NULL,
    `1_2` int(10) default NULL,
    `HR2` varchar(255) default NULL,
    `SOLD2` varchar(50) default NULL,
    `BN3` varchar(255) default NULL,
    `SB3` varchar(255) default NULL,
    `PORT3` varchar(255) default NULL,
    `XNAME3` varchar(255) default NULL,
    `FO3` varchar(255) default NULL,
    `DS3` varchar(255) default NULL,
    `YR3` varchar(255) default NULL,
    `BLDTYP3` varchar(255) default NULL,
    `BLDLOC3` varchar(255) default NULL,
    `L3` varchar(255) default NULL,
    `B3` varchar(255) default NULL,
    `SA3` varchar(255) default NULL,
    `1_3` int(10) default NULL,
    `DD3` varchar(255) default NULL,
    `HR3` varchar(255) default NULL,
    `SOLD3` varchar(50) default NULL,
    `BILL` bigint(1) unsigned default '0',
    `WINTER` bigint(1) unsigned default '0',
    `SPRING` bigint(1) unsigned default '0',
    `FALL` bigint(1) unsigned default '0',
    `RESIGNED` varchar(50) default NULL,
    `Non_US` bigint(1) unsigned default '0',
    `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    `sent_live` int(5) unsigned default NULL,
    `Secure_password_usr` varchar(100) default NULL,
    `Atempdate_usr` datetime default NULL,
    `Attemps_usr` int(11) unsigned default NULL,
    `middle_init_usr` varchar(11) default NULL,
    PRIMARY KEY (`CBA_ID`),
    UNIQUE KEY `s_GUID` (`s_GUID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;



    # Dump of table DUES_PAID
    # ------------------------------------------------------------

    CREATE TABLE `DUES_PAID` (
    `DUES_PAID_ID` int(10) NOT NULL auto_increment,
    `CBA_ID` int(10) default NULL,
    `CK_NBR` int(10) default NULL,
    `BANK_NBR` varchar(50) default NULL,
    `AMT_PAID` decimal(19,4) default NULL,
    `DATE_OF_DEP` datetime default NULL,
    `DUES_YEAR` int(10) default NULL,
    `Donation_Comments` varchar(50) default NULL,
    PRIMARY KEY (`DUES_PAID_ID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the DUES_PAID table has no index on CBA_ID

    so let's add one --
    Code:
    ALTER TABLE dues_paid
    ADD INDEX cba_id_x ( cba_id )
    now try your query again --
    Code:
    SELECT c.CBA_ID
         , c.LNAME
         , c.FN_MI
         , MAX(DUES_YEAR) AS max_dues_year
      FROM CBA AS c
    LEFT OUTER
      JOIN DUES_PAID AS d 
        ON d.CBA_ID = c.CBA_ID
    GROUP 
        BY c.CBA_ID
    note the subtle change in the GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •