#1
  1. A Change of Season
    Devshed Expert (3500 - 3999 posts)

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

    Need help to make query 100% optimized and index tables


    Hi;

    I got the numbers yesterday and I realize this system has to cater for prospects lists with maximum 100,000 visitors.

    This means there may be up to 100,000 simultaneous database connections. According to the docs, MySQL can easily handle that.

    That leaves us with 2 things: 1 - Proper server setup (enough memory etc) and 2 - Optimized well-done database design and properlly written queries.

    Here I want your advice to

    1 - How to index these 2 tables if necessary

    2 - Update queries if needed.

    I want Rudy's seal on it.

    Query 1:

    Code:
    SELECT timer_digit_bg, 
           timer_backdrop, 
           timer_digit_color, 
           timer_width, 
           timer_font_family, 
           timer_opacity, 
           timer_border_radius, 
           timer_position, 
           offer_close_date, 
           offer_close_time, 
           launch_timezone, 
           when_expires, 
           launch_status 
    FROM   launch_launches 
           INNER JOIN launch_owners 
                   ON launch_launches.user_id = launch_owners.id 
                      AND Md5(launch_owners.email) = ? 
                      AND launch_owners.status = 'active' 
    WHERE  launch_launches.id = ?
    ===> Explain <===


    Query 2:

    Code:
    UPDATE launch_launches 
    SET    timer_loads = timer_loads + 1 
    WHERE  id = ?
    ===> Explain <===


    Code:
    CREATE TABLE `launch_launches` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `launch_timezone` varchar(255) NOT NULL,
     `css_file` varchar(50) NOT NULL DEFAULT '1.css',
     `when_expires` varchar(255) NOT NULL DEFAULT 'do_nothing',
     `timer_digit_bg` varchar(255) NOT NULL DEFAULT '#000000',
     `timer_backdrop` varchar(255) NOT NULL DEFAULT 'transparent',
     `timer_digit_color` varchar(255) NOT NULL DEFAULT '#ffffff',
     `timer_width` varchar(55) NOT NULL DEFAULT 'smaller',
     `timer_font_family` varchar(255) NOT NULL DEFAULT 'Anton',
     `timer_opacity` varchar(255) NOT NULL DEFAULT '1',
     `timer_border_radius` varchar(255) NOT NULL DEFAULT '0',
     `timer_position` varchar(255) DEFAULT NULL,
     `launch_status` int(11) NOT NULL DEFAULT '1',
     `launch_timer_style` int(11) NOT NULL DEFAULT '1',
     `launch_timer_style_email` varchar(255) NOT NULL DEFAULT 'white',
     `launch_timer_email_font` varchar(255) NOT NULL DEFAULT 'arial',
     `title` varchar(255) NOT NULL,
     `launch_type` varchar(255) NOT NULL,
     `user_id` int(11) NOT NULL,
     `product_id` int(11) NOT NULL,
     `open_offer_x_days_or_hours_or_minutes_after_last_plc` int(3) NOT NULL DEFAULT '6',
     `evergreen_launch_days_hours_minutes` varchar(255) NOT NULL DEFAULT 'days',
     `offer_open_date` varchar(255) NOT NULL,
     `offer_open_time` varchar(255) NOT NULL,
     `offer_close_date` varchar(255) NOT NULL,
     `offer_close_time` varchar(255) NOT NULL,
     `sales_page` varchar(500) NOT NULL,
     `offer_closed_page` varchar(255) NOT NULL,
     `how_many_days_or_hours_or_minutes_keep_sales_page_open` varchar(255) NOT NULL DEFAULT '4',
     `keep_sales_page_open_days_or_hours_or_minutes` varchar(255) NOT NULL DEFAULT 'days',
     `report_mode` varchar(255) NOT NULL DEFAULT 'off',
     `timer_loads` int(11) NOT NULL DEFAULT '0',
     `thumbnail` varchar(255) NOT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=114 DEFAULT CHARSET=utf8
    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=2 DEFAULT CHARSET=utf8
    Thank you
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,793
    Rep Power
    4331
    here ya go -- seal.jpg
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,545
    Rep Power
    221
    Originally Posted by r937
    here ya go -- seal.jpg
    Hello;

    Thanks for the photo. But by a seal, I meant "approval", not a photo of a seal.

    Am I missing anything above in your opinion? Perhaps some indexes or a better-written join?

    Thank you Mr Rudy Limback. We love you long time
  6. #4
  7. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,820
    Rep Power
    9646
    Originally Posted by English Breakfast Tea
    This means there may be up to 100,000 simultaneous database connections. According to the docs, MySQL can easily handle that.
    Ha ha, no. Do you really think MySQL could handle up to 4.3 billion connections at once? You'll hit resource limits and bottlenecks before you get close to 100k.
    And 100k visitors does not mean 100k simultaneous database connections. More like 10k. Less if you're smart about your database access.


    Both those explains show const-time lookups getting single rows - because your queries are using primary keys. You can't get better than that.

    You should spend some time learning how to read EXPLAINs. They're not complicated.
  8. #5
  9. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,545
    Rep Power
    221
    Originally Posted by requinix
    Ha ha, no. Do you really think MySQL could handle up to 4.3 billion connections at once? You'll hit resource limits and bottlenecks before you get close to 100k.
    And 100k visitors does not mean 100k simultaneous database connections. More like 10k. Less if you're smart about your database access.
    Ok. Is this correct:

    1 billion visits a day = 1,000,000,000/(24*60*60) visits per second = almost 11,000

    Please note this is a very specific type of database access. These are timers installed on sales pages with specific expiry date-time. Meaning: Request increase at very specific times and the traffic isn't really spread over.

    That's the only reason I am so picky about it.

    Marketers send mail to their lists (max 100k prospects) and promote a sales page.

    When they visit the site, they see the timers.

    You saw what happened on Black Friday right? Scarcity makes people do crazy things.

    When I do product launch for my own products, most sales happen within the last 15 minutes of a 7-day launch. Strange.

    So I am thinking, if there is a launch to a 100k list, even if 10% of the list are active buyers and hit the sales page in the last few minutes of the launch, there will be easily a few k requests per second.

    No think about Christmas sales. How many businesses are gonna do launches that expire at midnight? Or valentines day etc etc.

    Are we on the same page? Or do you think I am very off?

    Also, we managed to make changes to the server, and now it handles +600 requests per second.

    These are the updates:
    Code:
    [mysqld]
    key_buffer_size=536870912
    #innodb_buffer_pool_size=536870912
    sql_mode="NO_ENGINE_SUBSTITUTION"
    open_files_limit = 50000
    performance-schema=0
    log-error=/var/log/mysqld.log
    innodb_file_per_table=1
    default-storage_engine=innodb
    innodb_buffer_pool_size=2048M   #<----allocates RAM for buffering InnoDB tables
    innodb_buffer_pool_instances=2  #<---- allocates multiple CPU instances for buffering
    innodb_log_file_size=128M
    max_connections=1000  #<----increased from 300
    key_buffer_size = 512M  #<-----allocates RAM for buffering MyISAM tables
    Last edited by English Breakfast Tea; June 11th, 2018 at 04:11 AM.
  10. #6
  11. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,674
    Rep Power
    1841
    I am sure there's a reason you are using the md5 hash of the email for comparison purposes as opposed to the actual email which must have been entered by the user at some stage. It will have minimal impact but would be a few less processor cycles per query. You have one date column type, but then also have many more as varchar. Not even going to comment on that column name that rivals War and Peace for length - at least with a name like that you can see how it is compensating for only being an int(3)
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,793
    Rep Power
    4331
    Originally Posted by SimonJM
    You have one date column type, but then also have many more as varchar.
    well spotted, good sir... waiting to hear OP's reason before rescinding my seal

    oh, and INT(3) holds exactly the same range of numbers as INT(9) or INT(37)...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,270
    Rep Power
    4193
    I would have expected the MD5 function call in the join condition hurt things. Assuming the explain is accurate though I guess it doesn't. Generally speaking running a function on a column as part of a where/join condition can slow things down and is best avoided.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  16. #9
  17. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,674
    Rep Power
    1841
    Originally Posted by kicken
    I would have expected the MD5 function call in the join condition hurt things. Assuming the explain is accurate though I guess it doesn't. Generally speaking running a function on a column as part of a where/join condition can slow things down and is best avoided.
    I was going to make a similar comment, but decided that the optimizeer is seeing the 'match' on the id columns and is using that as a driver for the index, which is efficient and only then doing the md5() malarkey.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  18. #10
  19. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,674
    Rep Power
    1841
    Originally Posted by r937
    well spotted, good sir... waiting to hear OP's reason before rescinding my seal

    oh, and INT(3) holds exactly the same range of numbers as INT(9) or INT(37)...
    Oh, I know, but I wanted a decent butt for the length of column name 'joke'
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  20. #11
  21. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,545
    Rep Power
    221
    Hello;

    Thanks for the replies.

    int (3), probably was a typo. Not that it matters much, but changed it to (11).

    A couple long column names... really couldn't find a better alternative.

    Column type date vs varchar: I don't perform date calculations in Mysql. In that case, type date only restricts me. I don't see missing out on much (unless I use the date functions).

    The end goal f the script is to provide this format for moment countdown timer library:

    Code:
    var date = moment.tz(expiry_date_time, "YYYY-MM-DD HH:mm", timezone);
    I am considering using DATETIME if I can store the values as 2018-12-12 20:45. I am pretty sure I can if I manually add: 00 for "seconds".

    Some forums and articles recommend using strtotime, some recommend MySql built-in functions.

    The big turn off was the difficulty of finding a way to match PHP and MySQL timezones.

    My current setup, not perfect, but seems to do the job. If you see any major catastrophic design decision here, point it out. It's very easy change.

    Tanx



    Broadcast campaign (Everyone sees the same expiry date)
    PHP Code:
    public function launch_details($hashed_owner_email$launch_id)
      {
        
    $CI =& get_instance();
        
    $sql "SELECT 
                timer_digit_bg
                ,timer_backdrop
                ,timer_digit_color
                ,timer_width
                ,timer_font_family
                ,timer_opacity
                ,timer_border_radius
                ,timer_position
                ,offer_close_date
                ,offer_close_time
                ,launch_timezone
                ,when_expires
                ,launch_status
                ,offer_closed_page
        FROM   launch_launches 
               INNER JOIN launch_owners 
                       ON launch_launches.user_id = launch_owners.id 
                          AND Md5(launch_owners.email) = ? 
                          AND launch_owners.status = 'active'
        WHERE  launch_launches.id = ? "
    ;

        
    $query $CI->db->query($sql, array($hashed_owner_email$launch_id));
        if(
    $query->num_rows()==1)
          {
            
    $results $query->result_array()[0];         
            foreach(
    $results as $val=>$row)
              {
                
    $CI->data[$val] = $row;
              }

            
    $CI->data['expiry_date_time'] = $CI->data['offer_close_date']." ".$CI->data['offer_close_time'];
            
    date_default_timezone_set($CI->data['launch_timezone']);
            
            
    $difference strtotime('now') - strtotime($CI->data['offer_close_date']." ".$CI->data['offer_close_time']);
              if(
    $difference 0)
                
    $CI->data['launch_status']='open';
              else 
                
    $_SESSION['errors'] = $CI->data['launch_status']='closed';
          }
        else 
            {
                
    $CI->data['launch_status']='invalid';
                
    $_SESSION['errors'] = "Invalid Or Inactive Campaign!";
            }        
      } 

    Evergreen Campaign (Each prospect gets their own expiry date time)
    PHP Code:
    public function launch_details_evergreen($hashed_owner_email$launch_id$prospect_email)
                {
                  
    $CI =& get_instance();

                
    $CI->data['PID']= $prospects_id $CI->launch_library->prospect_id_by_email($prospect_email);
                  
                  
    $sql "SELECT
                 timer_digit_bg
                ,timer_backdrop
                ,timer_digit_color
                ,timer_width
                ,timer_font_family
                ,timer_opacity
                ,timer_border_radius
                ,timer_position
                ,offer_close_date
                ,offer_close_time
                ,launch_timezone
                ,when_expires
                ,launch_status
                ,offer_closed_page 
                  FROM   launch_launches 
                         INNER JOIN launch_owners 
                                 ON launch_launches.user_id = launch_owners.id 
                                    AND Md5(launch_owners.email) = ? 
                  WHERE  launch_launches.id = ? "
    ;

                  
                  
    $query $CI->db->query($sql, array($hashed_owner_email$launch_id));
                  
    $results $query->result_array()[0];         
                  foreach(
    $results as $val=>$row)
                    {
                      
    $CI->data[$val] = $row;
                    }

                  
    $CI->data['expire_after_seconds'] = $CI->launch_library->expire_after_seconds();      
                  
    $CI->data['seconds_in_launch'] = $CI->launch_library->seconds_in_launch($prospects_id$launch_id); 
                  if(!
    $CI->data['seconds_in_launch'])
                    {
                      
    $_SESSION['errors'] = "Invalid Prospect";
                      return 
    false;
                    }  
                  
    $difference $CI->data['expire_after_seconds'] - $CI->data['seconds_in_launch'];
                    if(
    $difference 0)
                      {
                        
    $CI->data['launch_status']='closed';
                      }  
                    else 
                      {
                        
    $CI->data['launch_status']='open';     
                        
    $CI->data['expiry_date_time'] = date('Y-m-d H:i'strtotime("+ ".$difference." seconds"));
                      }
                      return 
    true;  
                } 
    Last edited by English Breakfast Tea; June 12th, 2018 at 12:38 AM.
  22. #12
  23. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,545
    Rep Power
    221
    Also, this app won't be doing any 'sorting' on dates columns. Ever.

    Only inserting and retrieving single records using WHERE id.

    I remember Requnix wasn't too crazy about using php to do the math but I am not sure why
    PHP Code:
    $difference strtotime('now') - strtotime($CI->data['offer_close_date']." ".$CI->data['offer_close_time']); 
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    89
    Rep Power
    72
    The argument is moot - you don't even need to know the difference. All you want to know is if close_date/time is less than now.
  26. #14
  27. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,545
    Rep Power
    221
    Originally Posted by Barand
    The argument is moot - you don't even need to know the difference. All you want to know is if close_date/time is less than now.
    Say that to SimonJM the moon is in his hand
  28. #15
  29. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,674
    Rep Power
    1841
    Originally Posted by English Breakfast Tea
    Say that to SimonJM the moon is in his hand
    'on', not 'in' - but thank you for having read that poem by Belloc, it is rather nice.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo