Improve query performance in MySQL

Question

I am posting this thread in order to have some advices regarding the performance of my SQL query. I have actually 2 tables, one which called HGVS_SNP with about 44657169 rows and another on run table which has an average of 2000 rows. When I try to update field Comment of my run table it takes lot's of time to perform the query. I was wondering if there is any method to increase my SQL query.

Structure of HGVS_SNP Table:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| snp_id    | int(11)     | YES  | MUL | NULL    |       |
| hgvs_name | text        | YES  |     | NULL    |       |
| source    | varchar(8)  | NO   |     | NULL    |       |
| upd_time  | varchar(32) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

My run table has the following structure:

+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| ID                   | varchar(7)   | YES  |     | NULL    |       |
| Reference            | varchar(7)   | YES  | MUL | NULL    |       |
| HGVSvar2             | varchar(120) | YES  | MUL | NULL    |       |
| Comment              | varchar(120) | YES  |     | NULL    |       |
| Compute              | varchar(20)  | YES  |     | NULL    |       |
+----------------------+--------------+------+-----+---------+-------+

Here's my query:

UPDATE run
INNER JOIN SNP_HGVS 
ON run.HGVSvar2=SNP_HGVS.hgvs_name
SET run.Comment=concat('rs',SNP_HGVS.snp_id) WHERE run.Compute not like 'tron'

The obviuos suspect is that the fields you join on have different types and hgvs_name seems to lack an index. — Joachim Isaksson
– Joachim Isaksson, Commented Apr 25, 2016 at 8:12
this not like is not lucky, and as I see, there is no index on Compute field — vaso123
– vaso123, Commented Apr 25, 2016 at 8:12
add index on Compute column and avoid LIKE if is not necessary — mitkosoft
– mitkosoft, Commented Apr 25, 2016 at 8:14
For NOT LIKE, is it okay to use it without any percent like "%tron%"? — rhavendc
– rhavendc, Commented Apr 25, 2016 at 8:16

Mihai · Accepted Answer · 2016-04-25 08:16:04Z

3

I`m guessing since you JOIN a text column with a VARCHAR(120) column that you don`t really need a text column. Make it a VARCHAR so you can index it

ALTER TABLE `HGVS_SNP` modify hgvs_name VARCHAR(120);

ALTER TABLE `HGVS_SNP` ADD KEY  idx_hgvs_name (hgvs_name);

This will take a while on large tables

Now your JOIN should be much faster,also add an index on compute column

ALTER TABLE `run` ADD KEY  idx_compute  (compute);

And the LIKE is unnecessary,change it to

WHERE run.Compute != 'tron'

answered Apr 25, 2016 at 8:16

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user979974 Over a year ago

Thanks to all for your replies :). @Mihai, thank you, your solution fits to me.

Collectives™ on Stack Overflow

Improve query performance in MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related