[Solved] Very slow query for large table

EverSQL Database Performance Knowledge Base

Very slow query for large table

Database type:

Although MySQL and queries are very fast in terms of execution, somehow mine is very slow.

Hardware:

Total Records in table = 16,83,410

Table Structure:

CREATE TABLE `test_result` (
  `parameter_id` varchar(45) NOT NULL,
  `parameter_value` varchar(5000) DEFAULT NULL,
  `test_id` varchar(45) NOT NULL,
  `package_id` varchar(45) DEFAULT NULL,
  `client_id` varchar(45) NOT NULL,
  `added_by` varchar(45) DEFAULT NULL,
  `added_on` varchar(45) DEFAULT NULL,
  `modify_by` varchar(45) DEFAULT NULL,
  `modify_on` varchar(45) DEFAULT NULL,
  `test_result_status` varchar(45) DEFAULT NULL,
  `analysis_comment` varchar(5000) DEFAULT NULL,
  `report_id` varchar(45) NOT NULL,
  PRIMARY KEY (`report_id`,`client_id`,`test_id`,`parameter_id`),
  KEY `ehr_test_result_index` (`report_id`,`client_id`,`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Query:

SELECT parameter_id,parameter_value
FROM test_result 
WHERE client_id = 14274 and report_id = 266432 and test_id = 21;

So, I'm executing query against single table, time taken to exeucte = 16 seconds :(

When I re-run the query, it takes 4 seconds, and on the third 1 seconds (looks like cache is being used or some sort of indexing)

How to optimize this query? Should I convert parameter_id, client_id, report_id, test_id to numeric type?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `test_result` ADD INDEX `test_result_idx_client_id_report_id_test_id` (`client_id`,`report_id`,`test_id`);
The optimized query:
SELECT
        test_result.parameter_id,
        test_result.parameter_value 
    FROM
        test_result 
    WHERE
        test_result.client_id = 14274 
        AND test_result.report_id = 266432 
        AND test_result.test_id = 21

Related Articles



* original question posted on StackOverflow here.