user979974 user979974 - 7 months ago 12
SQL Question

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'

Answer

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'