How Adding Indexes Cut a 30‑Second MySQL View Query to 2 Seconds

Background

The warehouse system consists of three business areas—stock in/out, inventory checks, and asset archives. data_task_asset stores in/out task details, data_check_asset_info stores inventory task details, and data_meter_info is the asset archive table. A view view_task_meter_info joins these tables to present task asset details together with human‑readable dictionary values.

Problem

When querying a single inventory‑check task containing more than 70,000 detail rows, the view took over 30 seconds to return results, which was unacceptable.

Diagnosis

Each dictionary lookup required a separate query. For 70,000 detail rows, the view performed roughly 700,000 dictionary lookups (10 lookups per row), each scanning over a thousand rows, causing the severe slowdown.

Solution 1 – Index the Dictionary Table

A composite index on domain and code was added to data_dictionary_info:

SELECT name FROM data_dictionary_info t01 WHERE t01.domain='设备类别' AND t01.code = t3.EQUIP_CATEG;

After the index, execution time fell to 2–3 seconds.

Solution 2 – Additional Indexes on Task Tables

Composite indexes were added on data_task_asset(task_id,task_no) and data_check_asset_info(chk_task_id,task_no). The query time increased slightly, and the execution plan showed that MySQL still ignored these indexes.

MySQL View Algorithms

When a view is created, MySQL chooses among three algorithms:

MERGE : The view definition is merged into the outer query, allowing index use.

TEMPTABLE : The view result is materialized in a temporary table, preventing index usage on the underlying tables.

UNDEFINED : MySQL decides automatically, preferring MERGE when possible.

Views that contain UNION / UNION ALL, GROUP BY, DISTINCT, aggregation functions, or HAVING force the UNDEFINED algorithm, which often falls back to TEMPTABLE.

Why UNION ALL Prevented Index Use

Removing the UNION ALL from the view allowed MySQL to use the index on the detail table, shaving about one second off the execution time.

Alternative Approaches

Split the single view into two separate views—one for in/out tasks and one for inventory tasks—and let the application choose the appropriate view.

Encapsulate the logic in a stored procedure that decides which query to run based on a flag.

Stored Procedure Example

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_task_meter_info`(IN `taskId` DECIMAL(16,0), IN `taskNo` VARCHAR(32), IN `ioFlag` VARCHAR(8))
BEGIN
  IF ioFlag = '盘点' THEN
    -- Return inventory‑check details
    SELECT * FROM ... WHERE ...;
  ELSE
    -- Return in/out details
    SELECT * FROM ... WHERE ...;
  END IF;
END

Calling the Procedure from Entity Framework

var dataListProc = DbContext.Database.SqlQuery<view_task_meter_info>(string.Format("CALL `sp_task_meter_info`({0}, '{1}','{2}')", searchModel.task_id, searchModel.task_no, searchModel.task_type)).ToList();