71 ) Performance tuning for - MY SQL

 

Sample Slow Running Query

SQL

SELECT 
    r.RegionName,
    s.StoreName,
    YEAR(o.OrderDate) AS OrderYear,
    MONTH(o.OrderDate) AS OrderMonth,
    COUNT(DISTINCT o.OrderID) AS TotalOrders,
    SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalRevenue,
    AVG(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS AverageOrderValue
FROM 
    Sales.Orders o
INNER JOIN 
    Sales.OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN 
    Sales.Stores s ON o.StoreID = s.StoreID
INNER JOIN 
    Sales.Regions r ON s.RegionID = r.RegionID
WHERE 
    r.RegionName = 'North America'
    AND o.OrderDate >= '2025-01-01' 
    AND o.OrderDate < '2025-02-01'
GROUP BY 
    r.RegionName,
    s.StoreName,
    YEAR(o.OrderDate),
    MONTH(o.OrderDate);

Steps to Resolve

Step 1: Set profiling and capture baseline performance

• Action: Enable query profiling in MySQL to measure the exact execution duration of our slow query before making any modifications.

• SQL Command:

  SQL

  SET profiling = 1;
  
  -- Execute the slow running query above
  SELECT 
      r.RegionName,
      s.StoreName,
      YEAR(o.OrderDate) AS OrderYear,
      MONTH(o.OrderDate) AS OrderMonth,
      COUNT(DISTINCT o.OrderID) AS TotalOrders,
      SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalRevenue,
      AVG(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS AverageOrderValue
  FROM 
      Sales.Orders o
  INNER JOIN 
      Sales.OrderDetails od ON o.OrderID = od.OrderID
  INNER JOIN 
      Sales.Stores s ON o.StoreID = s.StoreID
  INNER JOIN 
      Sales.Regions r ON s.RegionID = r.RegionID
  WHERE 
      r.RegionName = 'North America'
      AND o.OrderDate >= '2025-01-01' 
      AND o.OrderDate < '2025-02-01'
  GROUP BY 
      r.RegionName,
      s.StoreName,
      YEAR(o.OrderDate),
      MONTH(o.OrderDate);
  
  -- Check the query duration profile
  SHOW PROFILES;
  

• Sample Output (SHOW PROFILES):

  Plaintext

  Query_ID  Duration     Query
  1         4.85230000   SELECT r.RegionName, s.StoreName, YEAR(o.OrderDate)...
  

• Explanation of Output: The baseline execution time is 4.8523 seconds, indicating severe performance bottlenecks caused by full table scans and temporary sorting files.

Step 2: Run and analyze the execution plan using EXPLAIN FORMAT=TREE

• Action: Prepend EXPLAIN FORMAT=TREE to the slow query to visualize the hierarchical execution flow and access paths.

• SQL Command:

  SQL

  EXPLAIN FORMAT=TREE
  SELECT 
      r.RegionName,
      s.StoreName,
      YEAR(o.OrderDate) AS OrderYear,
      MONTH(o.OrderDate) AS OrderMonth,
      COUNT(DISTINCT o.OrderID) AS TotalOrders,
      SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalRevenue,
      AVG(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS AverageOrderValue
  FROM 
      Sales.Orders o
  INNER JOIN 
      Sales.OrderDetails od ON o.OrderID = od.OrderID
  INNER JOIN 
      Sales.Stores s ON o.StoreID = s.StoreID
  INNER JOIN 
      Sales.Regions r ON s.RegionID = r.RegionID
  WHERE 
      r.RegionName = 'North America'
      AND o.OrderDate >= '2025-01-01' 
      AND o.OrderDate < '2025-02-01'
  GROUP BY 
      r.RegionName,
      s.StoreName,
      YEAR(o.OrderDate),
      MONTH(o.OrderDate);
  

• Sample Output (EXPLAIN FORMAT=TREE):

  Plaintext

  -> Table scan on <temporary>  (actual time=0.001..0.002 rows=1 loops=1)
      -> Aggregate: count(distinct o.OrderID), sum(...), avg(...)  (cost=125430.20 rows=45200)
          -> Inner hash join (s.StoreID = o.StoreID)  (cost=125430.20 rows=45200)
              -> Table scan on s  (cost=12.50 rows=150)
              -> Inner hash join (r.RegionID = s.RegionID)  (cost=110020.10 rows=45200)
                  -> Filter: (r.RegionName = 'North America')  (cost=1.20 rows=5)
                      -> Table scan on r  (cost=1.20 rows=10)
                  -> Inner hash join (o.OrderID = od.OrderID)  (cost=98500.00 rows=452000)
                      -> Table scan on o  (cost=45200.00 rows=452000)
                      -> Table scan on od  (cost=48100.00 rows=1250000)
  

• Explanation of Output: The output renders an indented tree layout showing multiple Table scan operations on base tables o and od, forcing MySQL to read millions of rows from disk and create a heavy <temporary> table for aggregation.

Step 3: Create targeted indexes for joins and filters

• Action: Build structural indexes on foreign keys and filter targets to replace expensive table scans with fast index lookups.

• SQL Command:

  SQL

  ALTER TABLE Sales.Regions ADD INDEX idx_regions_name (RegionName);
  ALTER TABLE Sales.Stores ADD INDEX idx_stores_region (RegionID, StoreID);
  ALTER TABLE Sales.Orders ADD INDEX idx_orders_store_date (StoreID, OrderDate);
  ALTER TABLE Sales.OrderDetails ADD INDEX idx_orderdetails_covering (OrderID, Quantity, UnitPrice, Discount);
  

• Sample Output (SHOW INDEX):

  Plaintext

  Table        Non_unique  Key_name                    Seq_in_index  Column_name   Cardinality
  Regions      1           idx_regions_name            1             RegionName    2
  Stores       1           idx_stores_region           1             RegionID      2
  Stores       1           idx_stores_region           2             StoreID       150
  Orders       1           idx_orders_store_date       1             StoreID       150
  Orders       1           idx_orders_store_date       2             OrderDate     452000
  OrderDetails 1           idx_orderdetails_covering   1             OrderID       452000
  

• Explanation of Output: Confirms that the indexes are active and mapped correctly to the designated table columns.

Step 4: Update table statistics

• Action: Force InnoDB to recalculate internal index cardinalities and table distribution statistics.

• SQL Command:

  SQL

  ANALYZE TABLE Sales.Orders, Sales.OrderDetails, Sales.Stores, Sales.Regions;
  

• Sample Output (ANALYZE TABLE response):

  Plaintext

  Table                   Op        Msg_type  Msg_text
  Sales.Orders            analyze   status    OK
  Sales.OrderDetails      analyze   status    OK
  Sales.Stores            analyze   status    OK
  Sales.Regions           analyze   status    OK
  

• Explanation of Output: The OK status status confirms storage engine metrics are refreshed for the query optimizer.

Step 5: Verify memory buffer configuration

• Action: Check that the database buffer pool size is configured correctly to cache indexed data pages in system RAM.

• SQL Command:

  SQL

  SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
  

• Sample Output (SHOW VARIABLES):

  Plaintext

  Variable_name             Value
  innodb_buffer_pool_size   4294967296
  

• Explanation of Output: Returns the allocated memory buffer size (4 GB), verifying adequate memory caching to avoid disk input/output bottlenecks.

Step 6: Rewrite the query using Common Table Expressions (CTEs)

• Action: Restructure the query logic to isolate date ranges and region filters upfront using CTEs before aggregating metrics.

• Tuned SQL Query:

  SQL

  WITH FilteredOrders AS (
      SELECT 
          o.OrderID,
          o.StoreID,
          YEAR(o.OrderDate) AS OrderYear,
          MONTH(o.OrderDate) AS OrderMonth
      FROM Sales.Orders o
      INNER JOIN Sales.Stores s ON o.StoreID = s.StoreID
      INNER JOIN Sales.Regions r ON s.RegionID = r.RegionID
      WHERE r.RegionName = 'North America'
        AND o.OrderDate >= '2025-01-01' 
        AND o.OrderDate < '2025-02-01'
  ),
  AggregatedSales AS (
      SELECT 
          fo.OrderYear,
          fo.OrderMonth,
          fo.StoreID,
          COUNT(DISTINCT fo.OrderID) AS TotalOrders,
          SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS TotalRevenue,
          AVG(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS AverageOrderValue
      FROM FilteredOrders fo
      INNER JOIN Sales.OrderDetails od ON fo.OrderID = od.OrderID
      GROUP BY 
          fo.OrderYear,
          fo.OrderMonth,
          fo.StoreID
  )
  SELECT 
      r.RegionName,
      s.StoreName,
      a.OrderYear,
      a.OrderMonth,
      a.TotalOrders,
      a.TotalRevenue,
      a.AverageOrderValue
  FROM AggregatedSales a
  INNER JOIN Sales.Stores s ON a.StoreID = s.StoreID
  INNER JOIN Sales.Regions r ON s.RegionID = r.RegionID;
  

• Sample Output Execution Profile:

  SQL

  SET profiling = 1;
  -- [Run Fully Tuned Query]
  SHOW PROFILES;
  

  Plaintext

  Query_ID  Duration     Query
  2         0.14210000   WITH FilteredOrders AS (...) SELECT ...
  

• Explanation of Output: The execution time drops from 4.85230000 seconds down to 0.14210000 seconds, proving a massive 34x performance improvement.