How to improve MySQL query times
I am merging two datasets, each with ~ 1M rows, using Google SQL Cloud (MySQL 5.5 w / 4GB ram) and it takes over 5 hours to execute. I am running the following request from Sequel Pro:
create table newtable as (select * from table1 t1 left join table2 t2 using (key))
Each table has approximately 20 VARCHAR columns. The key is also VARCHAR.
I created an index for the key on both tables, but this did not change performance. I've searched a lot but can't find any direct advice on how to improve query times. Is this the expected query time for MySQL?
EDIT: each table ~ 250MB
source to share
The first thing I noticed is that your KEY is set to VARCHAR. This could be the main reason for the poor performance you are experiencing. This can be improved by adding an auto-incrementing one-piece PRIMARY KEY. Since each row of the millions of KEY values in Table 1 is individually matched to each of the millions of KEY values in Table 2, this makes the task highly efficient, moreover, by comparing between each of the characters in each of the String. Since using integers is a simple comparison, it will have much less impact.
The tier size of your Cloud SQl Instance will also have a big impact on performance due to the physical hardware constraints on your instance. You can temporarily change the level of your instance to test it in Edit Cloud SQL User Interface or using the Cloud SDK .
source to share