#1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,030
    Rep Power
    535

    Parent child table query


    I have the following database.

    Is there any reason I would not want to do the following:
    Code:
    SELECT p.*
    FROM parent AS p
    INNER JOIN other_table_has_child AS othc ON othc.child_parent_id=p.id
    WHERE p.site_id=123 AND othc.other_table_id=321;
    Reason I ask is I am not joining the "child" table since it provides no needed data, and am concerned that it will make my indexes inefficient. Thanks

    Code:
    SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
    SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
    SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
    
    CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
    USE `mydb` ;
    
    -- -----------------------------------------------------
    -- Table `mydb`.`site`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`site` (
      `id` INT UNSIGNED NOT NULL ,
      PRIMARY KEY (`id`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`parent`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`parent` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `data` VARCHAR(45) NULL ,
      `site_id` INT UNSIGNED NOT NULL ,
      PRIMARY KEY (`id`) ,
      INDEX `fk_parent_site1_idx` (`site_id` ASC) ,
      CONSTRAINT `fk_parent_site1`
        FOREIGN KEY (`site_id` )
        REFERENCES `mydb`.`site` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`child`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`child` (
      `parent_id` INT UNSIGNED NOT NULL ,
      `data` VARCHAR(45) NULL ,
      PRIMARY KEY (`parent_id`) ,
      CONSTRAINT `fk_table1_parent`
        FOREIGN KEY (`parent_id` )
        REFERENCES `mydb`.`parent` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`other_table`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`other_table` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
      `data` VARCHAR(45) NULL ,
      PRIMARY KEY (`id`) )
    ENGINE = InnoDB;
    
    
    -- -----------------------------------------------------
    -- Table `mydb`.`other_table_has_child`
    -- -----------------------------------------------------
    CREATE  TABLE IF NOT EXISTS `mydb`.`other_table_has_child` (
      `other_table_id` INT UNSIGNED NOT NULL ,
      `child_parent_id` INT UNSIGNED NOT NULL ,
      PRIMARY KEY (`other_table_id`, `child_parent_id`) ,
      INDEX `fk_other_table_has_child_child1_idx` (`child_parent_id` ASC) ,
      INDEX `fk_other_table_has_child_other_table1_idx` (`other_table_id` ASC) ,
      CONSTRAINT `fk_other_table_has_child_other_table1`
        FOREIGN KEY (`other_table_id` )
        REFERENCES `mydb`.`other_table` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_other_table_has_child_child1`
        FOREIGN KEY (`child_parent_id` )
        REFERENCES `mydb`.`child` (`parent_id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
    
    
    
    SET SQL_MODE=@OLD_SQL_MODE;
    SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
    SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,113
    Rep Power
    9398
    The only reason I can think of is really paranoid (read: unnecessary) data integrity: retrieve the parent details for #123 but only if it's the parent of child #321.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,030
    Rep Power
    535
    Thanks requinix, Good point, however, the application should keep that from happening (but there are those that would preach "never rely upon the application!")

    My biggest fear was not using indexes. I really don't know the internals on how they work. I have an index between Table A and Table B, and another index between Table B and Table C. Does that mean I have an index between Table A and Table C?
  6. #4
  7. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,113
    Rep Power
    9398
    It looks like you have some redundant indexes, actually. Remember that primary keys are automatically indexed.

    What does an EXPLAIN on that SELECT show?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,030
    Rep Power
    535
    What does an EXPLAIN on that SELECT show?
    Code:
    id    	select_type    	table    	type    	possible_keys    	key    	key_len    	ref    	rows    	Extra   
    1 	SIMPLE 	p 	ref 	PRIMARY,fk_parent_site1_idx 	fk_parent_site1_idx 	4 	const 	1 	
    1 	SIMPLE 	othc 	eq_ref 	PRIMARY,fk_other_table_has_child_child1_idx,fk_other_table_has_child_other_table1_idx 	PRIMARY 	8 	const,mydb.p.id 	1 	Using index
    What am I looking for? Also, off topic, but is there a better way for me to show tables on this forum?



    It looks like you have some redundant indexes, actually. Remember that primary keys are automatically indexed.
    Which ones? I am often lazy, create an ERD, and use MySQL Workbench to generate the schema. Looking at it, however, I so no redundant indexes. Yes, I have indexes on the FKs, and while the FKs are PKs in their respective tables, they are not PKs in the tables where I indexed them. Am I missing something?

    Thanks!
  10. #6
  11. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,113
    Rep Power
    9398
    Originally Posted by NotionCommotion
    What am I looking for?
    I'm not an expert but the first thing I look at is the "type". Values like "const" and "ref" and "eq_ref" are the best and mean MySQL can look up values fastest and most easily. There's also the "extra" where values talking about table scans and temporary tables and filesorts are generally bad (but sometimes unavoidable).
    That EXPLAIN has all the good and none of the bad so I'd say the query is great.

    Originally Posted by NotionCommotion
    Also, off topic, but is there a better way for me to show tables on this forum?
    mysqldump or SHOW CREATE TABLE or whatever SQL you use to create the table is fine for me personally.

    Originally Posted by NotionCommotion
    Which ones?
    Hmm. When I said "some" I meant "one". other_table_has_child has a PK on other_table_id+child_parent_id (and thus an index on that) as well as an index on other_table_id. An index across columns A+B+C is equivalent to having three indexes on A, A+B, and A+B+C. Left-to-right. So the index on the PK automatically includes an index on just other_table_id and thus the dedicated index is unnecessary. Meanwhile the one on child_parent_id isn't already covered so it's still necessary.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,030
    Rep Power
    535
    On my off topic question about how best to show a table, I didn't mean a SQL table, but how to format the table on this forum so the columns line up. After re-reading my post, there was no way you or anyone could have know what I was asking!

    In regards to other_table_id+child_parent_id, I see your point. Strange, however, that MySQL Workbench automatically includes those indexes.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Originally Posted by NotionCommotion
    ... how to format the table on this forum so the columns line up.
    use [code]...[/code] tags and plenty of spaces
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,030
    Rep Power
    535
    use [ code ]...[ /code ] tags and plenty of spaces
    Had the [code] but not the spaces. Next time!

    Any suggestions why MySQL Workbench automatically includes indexes both on composite PKs as well as the individual keys?

    PS. EDIT-Don't put [CODE] inside of [QUOTEs], it doesn't work!
    Last edited by NotionCommotion; December 14th, 2012 at 07:14 PM.
  18. #10
  19. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,113
    Rep Power
    9398
    Originally Posted by r937
    use [code]...[/code] tags and plenty of spaces
    You mean like this?
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Originally Posted by requinix
    You mean like this?
    Code:
    no...
    
    like
              this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,113
    Rep Power
    9398
    Originally Posted by r937
    Code:
    no...
    
    like
              this
    Heh. That was supposed to be a reply to Notion, not you Putting [code]s inside [quote]s and whatnot.
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,030
    Rep Power
    535
    I really don't know anymore... I did a reply to r937's post and enclosed his reply in quotes, and upon later viewing it, the format was whacked. I've since tried to reproduce the effect but fortunately with no success. So all's well that ends well...
  26. #14
  27. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,113
    Rep Power
    9398
    Originally Posted by NotionCommotion
    I really don't know anymore... I did a reply to r937's post and enclosed his reply in quotes, and upon later viewing it, the format was whacked. I've since tried to reproduce the effect but fortunately with no success. So all's well that ends well...
    He put [color=white][/color]s in there to break the tags up so they wouldn't get translated. Clicking the Reply button preserves all that, but if you tried copy/pasting what he wrote then you'd lose that.
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,030
    Rep Power
    535
    Originally Posted by requinix
    He put [color=white][/color]s in there to break the tags up so they wouldn't get translated. Clicking the Reply button preserves all that, but if you tried copy/pasting what he wrote then you'd lose that.
    Ah, I see... He is very tricky, you know

IMN logo majestic logo threadwatch logo seochat tools logo