Mysql – Improve select query performance

codeformatMySQLperformancequery-performance

The following select query from my application is taking more than 200ms. The table has around 2 million rows. Is there any ways to improve the performance of this query ?

select count(id) from table1 where status='A' and empID=123

Table Indexes

PRIMARY KEY (`id`),        

KEY `empID` (`empID`),               
KEY `status` (`status`),    
KEY `testID` (`testID`),    

CONSTRAINT `fk_1` FOREIGN KEY (`empID`) REFERENCES `emp_info` (`id`)

Best Answer

A composite index on (status, empID) will be faster. It might be better as (empID, status) depending on the cardinality of these two columns. An EXPLAIN would have shown you that it was only using one value against one index.

Related Solutions

Mysql – Would adding indexes to the foreign keys improve performance on this MySQL query

Yes, performance may be better if you add those indexes. However, with such a small number of rows, it's quite possible that full table scan is more efficient and optimizer choses not to use any indexes.
After adding indexes your execution plan will be different, to get a rough estimation of how effective the indexes are you can multiply "Rows" column for each line of output of explain
In general, indexes on fields which participate in filtering/join conditions/order/group by improve performance. You also need to take into account selectivity (how many distinct values you have) of the column; if it's too low , the engine will not use it except if it's covering index for a query.
Foreign key is a constraint; the main purpose of any constraint to enforce some restriction (referential integrity in case of FK). Thus, if you care about integrity of you data, you should add foreign constraint.

The fact that Mysql implicitly creates an index on FK column means better read performance, and bit worse insert/update/delete performance (because index itself has to be updated).

Finally,

My thinking is that since I'm selecting from locations first, ....

is not absolutely correct. Physical processing is not the same as logical; optimizer decides in which order it will process tables involved (as you can see in your output, the engine first accesses tickets table) and what access method to use. You can control it to some extent with hints though...

*Side note. The way your WHERE clause written :

WHERE `tickets`.`client_id` = '20'
  AND
  (
    `customers`.`name` LIKE '%Mahoney%'
    OR `customers`.`email` LIKE '%Mahoney%'
    OR `locations`.`address` LIKE '%Mahoney%'
  )

makes your LEFT JOIN customers behave as INNER JOIN.*

Update Never mind my side note, I didn't pay attention you have ORs with multiple tables.

I hope it was helpful.

Sql-server – Improve Performance -Update query with OR in WHERE

The best option if you really want to squeeze out the most is to parallelise the query.

Instead of a single UPDATE, run multiple copies. Each of them operate on a subset of the data in the temporary table. Keep adding more copies and more subsets until you run out of cores of CPU (whatever happens first).

If you don't have the ability to parallelise. Run two UPDATES, one that works on the first part of the OR, another on the second. If possible, add indexes on t_h_bg_pc_it to support both queries. Like this:

Declare @date datetime = '09-Dec-2013' 
update  #tmp_sp_abc  
set     test = rtrim(xyz_test)  
from    #tmp_sp_abc t1
join    t_h_bg_pc_it t2 
  on    t2.id_i = t1.i
where   t1.r_type = 1  
and     t2.[date] = @date

update  #tmp_sp_abc  
set     test = rtrim(xyz_test)  
from    #tmp_sp_abc t1
join    t_h_bg_pc_it t2 
  on    tt2.id_s1 = t1.s
where   t1.r_type = 1  
and     t2.[date] = @date

You can now index t_h_bg_pc_it on both s, date, xyz_test and id_id, date, xyz_test (two indexes). This should speed things up. It may also be useful to index #tmp_sp_abc on r_type. If possible, do both the parallelism and the indexing/rewrite.

Finally: Fire whoever it is that comes up with naming conventions for your tables. Life is too short for this type of cryptic naming, SQL Server does auto-completion.

Best Answer

Related Solutions

Mysql – Would adding indexes to the foreign keys improve performance on this MySQL query

Sql-server – Improve Performance -Update query with OR in WHERE

Related Question