#1
  1. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,479
    Rep Power
    221

    Which query is preferred?


    If jesus was still among us, which query would he use to get the timezone using plc_id?
    Code:
    SELECT timezone 
    FROM   launch_owners 
           INNER JOIN launch_plcs 
                   ON launch_plcs.user_id = launch_owners.id 
                      AND launch_plcs.id = ?
    Code:
    SELECT timezone 
    FROM   launch_plcs 
           INNER JOIN launch_owners 
                   ON launch_plcs.user_id = launch_owners.id 
    WHERE  launch_plcs.id = ?
    Code:
    CREATE TABLE `launch_owners` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `secret_key` varchar(255) NOT NULL,
     `email` varchar(255) NOT NULL,
     `date_added` date NOT NULL,
     `status` varchar(255) NOT NULL,
     `timezone` varchar(255) NOT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2314 DEFAULT CHARSET=utf8
    Code:
    CREATE TABLE `launch_plcs` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `url` varchar(255) NOT NULL,
     `launch_id` int(11) NOT NULL,
     `page_loads` int(11) NOT NULL DEFAULT '0',
     `plc_views` int(11) NOT NULL DEFAULT '0',
     `user_id` int(11) NOT NULL,
     `release_after_days_evergreen` int(11) NOT NULL DEFAULT '1',
     `page_title_for_browser` varchar(255) NOT NULL,
     `title` varchar(255) NOT NULL,
     `youtube_video` varchar(255) NOT NULL DEFAULT 'XJWmSlsGxig',
     `plc_video` text NOT NULL,
     `broadcast_status` int(11) DEFAULT NULL,
     `broadcast_access_date` varchar(255) DEFAULT NULL,
     `broadcast_redirect_if_before_access_date` varchar(255) DEFAULT NULL,
     `evergreen_status` int(11) DEFAULT '1',
     `sub_headline` varchar(255) NOT NULL DEFAULT 'SUB HEADLINE',
     `main_headline` varchar(255) NOT NULL DEFAULT 'MAIN HEADLINE',
     `magic_timer` varchar(255) NOT NULL DEFAULT 'no',
     `show_magic_content_after_seconds` int(11) NOT NULL DEFAULT '5',
     `countdown_timer_magic` int(11) NOT NULL DEFAULT '0',
     `buy_button_magic` int(11) NOT NULL DEFAULT '0',
     `comments_section_magic` int(11) NOT NULL DEFAULT '0',
     `video_frame_bg` varchar(255) NOT NULL,
     `show_launch_nav` int(11) NOT NULL DEFAULT '0',
     `show_offer_open_timer` int(11) NOT NULL DEFAULT '0',
     `show_comments` int(11) NOT NULL DEFAULT '0',
     `show_buy_button` int(11) NOT NULL DEFAULT '0',
     `show_footer` int(11) NOT NULL DEFAULT '0',
     `footer_background_color` varchar(255) NOT NULL DEFAULT '#000000',
     `footer_text` text NOT NULL,
     `buy_button_background_color` varchar(255) NOT NULL DEFAULT '#F2F2F2',
     `buy_button_text` varchar(60) NOT NULL DEFAULT 'ADD TO CART',
     `buy_button_link` varchar(255) NOT NULL DEFAULT 'HTTP://GOOGLE.COM',
     `comment_type` varchar(255) NOT NULL DEFAULT 'manual',
     `header_background_color` varchar(255) NOT NULL DEFAULT '#000000',
     `pre_headline_color` varchar(255) NOT NULL DEFAULT '#FFFFFF',
     `main_headline_color` varchar(255) NOT NULL DEFAULT '#FFFFFF',
     `video_row_background_color` varchar(255) NOT NULL DEFAULT '#ffffff',
     `timer_row_background_color` varchar(255) NOT NULL DEFAULT '#000000',
     `buy_button_row_background_color` varchar(255) NOT NULL DEFAULT '#000000',
     `video_embed_type` varchar(25) NOT NULL DEFAULT 'video_with_embed',
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=43258 DEFAULT CHARSET=utf8
  2. #2
  3. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,751
    Rep Power
    9646
    Post the outputs of an EXPLAIN SELECT... for both queries.
  4. #3
  5. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,252
    Rep Power
    4150
    They should be identical after they are parsed I believe, so I doubt it matters from a technical perspective.

    Personally, I like to structure my queries so that the start with what I'd consider the root of the relationships and build out to the info/tables I need from there. That translates to using the first query for you since the owners are the root, and each owner has a set of plc's that you're querying against.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  6. #4
  7. A Change of Season
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,479
    Rep Power
    221
    Originally Posted by requinix
    Post the outputs of an EXPLAIN SELECT... for both queries.
    EXPLAINS are exactly the same.

    I can write some sql but I can't really explain how I know what to do. SimplySql helped but a lot to learn here.
  8. #5
  9. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,751
    Rep Power
    9646
    Originally Posted by English Breakfast Tea
    EXPLAINS are exactly the same.
    Indeed.

    If it's any consolation, I can't really explain how you know what to do either

    Here's what I see when I read the queries:
    Code:
    SELECT timezone 
    FROM   launch_owners 
           INNER JOIN launch_plcs 
                   ON launch_plcs.user_id = launch_owners.id 
                      AND launch_plcs.id = ?
    Start in the launch_owners table. Match each record in it to the launch_plcs table based on the id and user_id, but only when there's a matching id.
    and
    Code:
    SELECT timezone 
    FROM   launch_plcs 
           INNER JOIN launch_owners 
                   ON launch_plcs.user_id = launch_owners.id 
    WHERE  launch_plcs.id = ?
    Start in the launch_plcs table. Match each record in it to the launch_owners table based on the user_id and id. Restrict the results to those with a matching id.
    The EXPLAINs will tell you what MySQL will (probably) do when it tries to execute the queries. Since they're so similar the optimizer recognizes that they both have the same basic parts: involves the launch_owners and launch_plcs tables, matches rows based on the id and user_id, and only keeps records with the right id.

    In both cases it will start with the launch_plcs table, find rows matching the id (there will only be one), then add the launch_owners table. Effectively,
    Code:
    SELECT timezone FROM launch_owners WHERE id = (SELECT user_id FROM launch_plcs WHERE id = ?)

IMN logo majestic logo threadwatch logo seochat tools logo