CODEWITHSUNDEEP
mysqlsqlperformanceselectjoin
user979974
user979974

Reputation: 953

Improve query performance in MySQL

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'

Upvotes: 3

Views: 88

Answers (1)

Mihai
Mihai

Reputation: 26784

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'

Upvotes: 3

Related Questions