title: MySql query optimization approach
body: <p>I am working on a dashboard in PHP MySql, where users will login and access the dashboard, 
  user may have access to single section and single country or multiple sections and multiple countries.</p>
  
  <p>i have made 3 predefined views and from the predefined views i am inserting into summary tables
  and allowing the users to access the summary tables.</p>
  
  <p>these are my predefined views</p>
  
  <p><strong>unit_details</strong></p>
  
  <pre><code>          select 
        sections.section_id,
        countries.country_id,
        business_units.unit_id,
        sections.section_name,
        countries.country_name,
        cities.city_name,
        business_units.unit_name,
        business_unit_types.unit_type_name,
        business_unit_categories.unit_category_name
        from
        sections,
        countries,
        cities,
        business_units,
        business_unit_types,
        business_unit_categories 
        where 
             business_units.section_id=sections.section_id
        and  business_units.country_id=countries.country_id
        and  business_units.city_id=cities.city_id
        and  business_units.unit_type_id=business_unit_types.unit_type_id
        and  business_units.unit_category_id=business_unit_categories.unit_category_id
        and  cities.country_id=countries.country_id;
  </code></pre>
  
  <p><strong>transaction_details</strong></p>
  
  <pre><code>          SELECT
        transactions.business_date,
        transactions.transaction_datetime,
        business_unit_product_category_section.section_id,
        business_units.country_id,
        transactions.unit_id,
        transactions.transaction_id,
        product_category_groups.product_category_group_name,
        transactions.product_category_id,
        product_categories.product_category_name,
        transactions.product_id,
        products.product_name,
        transactions.net_sales
        FROM 
        transactions,
        business_unit_product_category_section,
        business_units,
        products,
        product_categories,
        product_category_groups 
        where 
            transactions.unit_id=business_unit_product_category_section.unit_id
        and transactions.product_category_id=business_unit_product_category_section.product_category_id
        and transactions.unit_id=business_units.unit_id 
        and business_unit_product_category_section.section_id=business_units.section_id 
        and business_unit_product_category_section.unit_id=business_units.unit_id
        and transactions.product_id=products.product_id 
        and transactions.product_category_id=products.product_category_id
        and transactions.product_category_id=product_categories.product_category_id
        and product_categories.product_category_id=products.product_category_id
        and product_categories.product_category_group_id=product_category_groups.product_category_group_id;
  </code></pre>
  
  <p><strong>final view</strong></p>
  
  <pre><code>          select 
        unit_details.section_name,
        unit_details.country_name,
        unit_details.city_name,
        unit_details.unit_name,
        unit_details.unit_type_name,
        unit_details.unit_category_name,
        transaction_details.business_date,
        transaction_details.transaction_datetime,
        transaction_details.section_id,
        transaction_details.country_id,
        transaction_details.unit_id,
        transaction_details.transaction_id,
        transaction_details.product_category_group_name,
        transaction_details.product_category_id,
        transaction_details.product_category_name,
        transaction_details.product_id,
        transaction_details.product_name,
        transaction_details.net_sales
        from unit_details ud
        left join transaction_details td on 
        td.section_id=ud.section_id
        and 
        td.country_id=ud.country_id
        and 
        td.unit_id=ud.unit_id;
  </code></pre>
  
  <p>this is one of my summary table query
  the summary tables updated every 30 minutes by a batch file executing the SQL.</p>
  
  <pre><code>          SET @date_today = DATE(NOW());
        select
        final_view.section_name,
        final_view.country_name,
        final_view.city_name,
        final_view.unit_name,
        final_view.unit_type_name,
        final_view.unit_category_name,
        sum(CASE WHEN @date_today = final_view.business_date THEN final_view.net_sales ELSE 0 END) TODAYS_NETSALES,
        sum(CASE WHEN month(@date_today) = month(final_view.business_date) and final_view.business_date&lt;=@date_today THEN final_view.net_sales ELSE 0 END) MTD_NETSALES
        from final_view
        group by final_view.section_name,final_view.country_name,final_view.city_name,final_view.unit_name,final_view.unit_category_name;
  </code></pre>
  
  <p>this is my schema</p>
  
  <pre><code>          CREATE TABLE business_units (
          id int(11) NOT NULL,
          unit_id int(11) NOT NULL,
          unit_name varchar(30) NOT NULL,
          section_id int(11) NOT NULL,
          country_id int(11) NOT NULL,
          city_id int(11) NOT NULL,
          unit_type_id int(11) NOT NULL,
          unit_category_id int(11) NOT NULL,
          created_by varchar(30) NOT NULL,
          created_datetime datetime NOT NULL,
          is_active_status int(1) NOT NULL COMMENT '1-active, 0-not active',
          status_change_datetime datetime NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
        CREATE TABLE business_unit_categories (
          unit_category_id int(11) NOT NULL,
          unit_category_name varchar(30) NOT NULL,
          created_by varchar(30) NOT NULL,
          created_datetime datetime NOT NULL,
          is_active_status int(1) NOT NULL COMMENT '1-active, 0-not active',
          status_change_datetime datetime NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
        CREATE TABLE business_unit_product_category_section (
          id int(11) NOT NULL,
          unit_id int(11) NOT NULL,
          product_category_id int(11) NOT NULL,
          section_id int(11) NOT NULL,
          created_by varchar(30) NOT NULL,
          created_datetime datetime NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
        CREATE TABLE business_unit_types (
          unit_type_id int(11) NOT NULL,
          unit_type_name varchar(30) NOT NULL,
          created_by varchar(30) NOT NULL,
          created_datetime datetime NOT NULL,
          is_active_status int(1) NOT NULL COMMENT '1-active, 0-not active',
          status_change_datetime datetime NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  
        CREATE TABLE cities (
          city_id int(11) NOT NULL,
          city_name varchar(30) NOT NULL,
          country_id int(11) NOT NULL,
          created_by varchar(30) NOT NULL,
          created_datetime datetime NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  
        CREATE TABLE countries (
          country_id int(11) NOT NULL,
          country_name varchar(30) NOT NULL,
          created_by varchar(30) NOT NULL,
          created_datetime datetime NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  
        CREATE TABLE products (
          id int(11) NOT NULL,
          product_id varchar(13) NOT NULL,
          product_name varchar(300) NOT NULL,
          product_category_id int(11) NOT NULL,
          created_by varchar(30) NOT NULL,
          created_datetime datetime NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  
        CREATE TABLE product_categories (
          product_category_id int(11) NOT NULL,
          product_category_name varchar(30) NOT NULL,
          product_category_group_id int(11) NOT NULL,
          created_by varchar(30) NOT NULL,
          created_datetime datetime NOT NULL,
          is_active_status int(1) NOT NULL COMMENT '1-active, 0-not active',
          status_change_datetime datetime NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  
        CREATE TABLE product_category_groups (
          product_category_group_id int(11) NOT NULL,
          product_category_group_name varchar(30) NOT NULL,
          created_by varchar(30) NOT NULL,
          created_datetime datetime NOT NULL,
          is_active_status int(1) NOT NULL COMMENT '1-active, 0-not active',
          status_change_datetime datetime NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
        CREATE TABLE sections (
          section_id int(11) NOT NULL,
          section_name varchar(30) NOT NULL,
          created_by varchar(30) NOT NULL,
          created_datetime datetime NOT NULL,
          is_active_status int(1) NOT NULL COMMENT '1-active, 0-not active',
          status_change_datetime datetime NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  
        CREATE TABLE transactions (
          id int(11) NOT NULL,
          business_date date NOT NULL,
          unit_id int(11) NOT NULL,
          transaction_id int(11) NOT NULL,
          transaction_datetime datetime NOT NULL,
          product_category_id int(11) NOT NULL,
          product_id varchar(13) NOT NULL,
          net_sales float NOT NULL,
          net_qty int(11) NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
        CREATE TABLE user_permissions (
        id int(11) NOT NULL,
        user_id varchar(30) NOT NULL,
        section_id int(11) NOT NULL,
        country_id int(11) NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  
        ALTER TABLE business_units
          ADD PRIMARY KEY (id),
          ADD UNIQUE KEY unit_id (unit_id,section_id,country_id),
          ADD KEY unit_id_2 (unit_id,section_id,country_id),
          ADD KEY city_id (city_id),
          ADD KEY unit_type_id (unit_type_id),
          ADD KEY unit_category_id (unit_category_id);
  
  
        ALTER TABLE business_unit_categories
          ADD PRIMARY KEY (unit_category_id);
  
  
        ALTER TABLE business_unit_product_category_section
          ADD PRIMARY KEY (id),
          ADD UNIQUE KEY unit_id (unit_id,product_category_id,section_id),
          ADD KEY unit_id_2 (unit_id,product_category_id,section_id);
  
  
        ALTER TABLE business_unit_types
          ADD PRIMARY KEY (unit_type_id);
  
  
        ALTER TABLE cities
          ADD PRIMARY KEY (city_id),
          ADD UNIQUE KEY city_id (city_id,country_id),
          ADD KEY country_id (country_id),
          ADD KEY city_id_2 (city_id,country_id);
  
  
        ALTER TABLE countries
          ADD PRIMARY KEY (country_id);
  
  
        ALTER TABLE products
          ADD PRIMARY KEY (id),
          ADD KEY product_id (product_id),
          ADD KEY product_category_id (product_category_id);
  
  
        ALTER TABLE product_categories
          ADD PRIMARY KEY (product_category_id),
          ADD UNIQUE KEY product_category_id (product_category_id,product_category_group_id),
          ADD KEY product_category_group_id (product_category_group_id);
  
  
        ALTER TABLE product_category_groups
          ADD PRIMARY KEY (product_category_group_id);
  
  
        ALTER TABLE sections
          ADD PRIMARY KEY (section_id);
  
  
        ALTER TABLE transactions
          ADD PRIMARY KEY (id),
          ADD KEY business_date (business_date),
          ADD KEY unit_id (unit_id),
          ADD KEY transaction_id (transaction_id),
          ADD KEY transaction_datetime (transaction_datetime),
          ADD KEY product_category_id (product_category_id),
          ADD KEY product_id (product_id),
          ADD KEY product_id_3 (product_id,product_category_id);
  
  
        ALTER TABLE transactions
          MODIFY id int(11) NOT NULL AUTO_INCREMENT;
  
        ALTER TABLE user_permissions
        ADD PRIMARY KEY (id),
        ADD UNIQUE KEY user_id_3 (user_id,section_id,country_id),
        ADD KEY user_id (user_id),
        ADD KEY section_id (section_id),
        ADD KEY country_id (country_id),
        ADD KEY user_id_2 (user_id,section_id,country_id);
  
        ALTER TABLE user_permissions
        MODIFY id int(11) NOT NULL AUTO_INCREMENT;
  </code></pre>
  
  <p>my question is, </p>
  
  <p>whether it is advisable to create predefined views like the above and do selects from predefined view and inserting into summary tables?</p>
  
  <p>or should i remove the predefined views and summary tables and stop the batch and generate the summary view through PHP page only when users accessing the dashboard during the session?</p>
  
  <p>the reason why i have created the predefined views is, the transactions table got millions of record and it takes around 10 to 15 minutes to update the summary tables. and when the user access the dashboard the data is available and user need not wait to see the data. </p>
  
  <p>if i generate the data during the session user will have to wait 10 to 15 minutes to see the data.</p>
  
  <p>kindly advice on proper approach and also on help me to optimize the sql query.</p>