Search code examples
mysqlsqldatabasesql-tuning

SQL Query Performence is too bad for MySQL

I run the following SQL Query on a MySQL platform.

Table A is a table which has a single column (primary key) and 25K rows. Table B has several columns and 75K rows.

It takes 20 minutes to execute following query. I will be glad if you could help.

INSERT INTO sometable
SELECT A.PrimaryKeyColumn as keyword, 'SomeText', B.*
FROM A, B
WHERE B.PrimaryKeyColumn = CONCAT(A.PrimaryKeyColumn, B.NotUniqueButIndexedColumn);
Solution

  • Run the SELECT without the INSERT to see if the problem is with the SELECT or not.

    If it is with the SELECT, follow the MySQL documentation explaining how to optimize queries using EXPLAIN.

    If the SELECT runs fine but the INSERT takes forever, make sure you don't have a lot of unnecessary indexes on sometable. Beyond that, you may need to do some MySQL tuning and/or OS tuning (e.g., memory or disk performance) to get a measurable performance boost with the INSERT.