CODEWITHSUNDEEP
phpmysqlsqldatabasedatabase-design
davidb
davidb

Reputation: 273

MySql query optimization approach

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.

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.

these are my predefined views

unit_details

          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;

transaction_details

          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;

final view

          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;

this is one of my summary table query the summary tables updated every 30 minutes by a batch file executing the SQL.

          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<=@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;

this is my schema

          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;

my question is,

whether it is advisable to create predefined views like the above and do selects from predefined view and inserting into summary tables?

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?

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.

if i generate the data during the session user will have to wait 10 to 15 minutes to see the data.

kindly advice on proper approach and also on help me to optimize the sql query.

Upvotes: 0

Views: 102

Answers (2)

Rick James
Rick James

Reputation: 142298

  • Unclutter - I agree with Strawberry's textual changes.
  • Unclutter - Don't normalize Country, just include it in City.
  • Unclutter - Do you really need created_by and created_datetime.
  • Unclutter - Don't use 5-word table names.
  • Use JOIN ... ON ... instead of the old "comma join".
  • Don't have both INDEX(a) and INDEX(a,b); the former is redundant and unnecessary.
  • A UNIQUE index is an index, so INDEX(a,b) is unnecessary when you also have UNIQUE(a,b).

Back to some of your question...

  • A VIEW (in MySQL) is syntactic sugar -- it can never be faster than the equivalent SELECT. (Though it may be easier to read.)
  • There is no explicit support for Summary Tables in MySQL (even with VIEWs). However manually implementing such is a good idea for performance; I sometimes see 10-times performance improvement.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

Not an answer. Too long for a comment.

We would normally write these sorts of queries this way. I suggest you start here and amend your question (and possibly your data model) accordingly...

SELECT s.section_id
     , c.country_id
     , u.unit_id
     , s.section_name
     , c.country_name
     , x.city_name
     , u.unit_name
     , t.unit_type_name
     , y.unit_category_name
  FROM sections s
  JOIN business_units u
    ON u.section_id = s.section_id
  JOIN countries c
    ON u.country_id = c.country_id
  JOIN cities x
    ON x.city_id = u.city_id 
   AND x.country_id = c.country_id -- there is a redundancy in your model here
  JOIN business_unit_types t
    ON t.unit_type_id = u.unit_type_id 
  JOIN business_unit_categories y
    ON y.unit_category_id u.unit_category_id

Upvotes: 2

Related Questions