Optimizing subqueries and joins in MySQL
I have a problem:
I have a table with name value pairs:
Key1 Data11 id1
Key2 Data12 id1
Key1 Data21 id2
Key2 Data22 id2
I also have a master id table:
id1 name1
id2 name2
I wanted to create a sortable table reading from this mysql database that looked like:
Importantly I want to be able to sort by multiple fields.
My SQL looked something like this:
select col1, col2, col3 from t1 join (select col as col2 from t2 where name='data1') t2 on t1.id=t2.id join (select col as col3 from t2 where name='data1') t3 order by col2, col3 limit 5
This recursively broke the mysql engine, as I have actually got 100000 records in both tables.
I found this article: here on the problem, which was helpful, but didn't solve my problem: all the keys in the world didn't optimize the subqueries once there was more than one, and I need the data out of the subqueries to display in my table!
Finally after much trial and error I found creating a view was the answer. A view describing the two subqueries now has an execution plan with only one table scan, and additional columns can be added without much performance hit.
Full SQL now looks like:
CREATE VIEW v_col2 AS
SELECT * FROM t1 where name='Data2';
CREATE VIEW v_col3 AS
SELECT * FROM t2 where name='Data1';
SELECT col1,t2.data as col2,t3.data as col3 from t1 join v_col2 t2 on t1.id=t2.id join v_col3 t3 order by col2, col3 limit 5
The first two lines are only run if they don't exist already
Interestingly temporary tables didn't work quite as well (still wanted a full row scan - but I may have missed a key), but I didn't like them anyway as they are not dynamically updated.
How that helps someone! I have not tried a similar test in other DB engines, and am curious how ORACLE or MSSQL perform. SQL 2000 also had issues with recursively running joins in my experience, maybe 2005 has fixed it?
I have a table with name value pairs:
Key1 Data11 id1
Key2 Data12 id1
Key1 Data21 id2
Key2 Data22 id2
I also have a master id table:
id1 name1
id2 name2
I wanted to create a sortable table reading from this mysql database that looked like:
ID KEY1 KEY2
--- ---- ----
id1 Data11 Data12
id2 Data21 Data22
Importantly I want to be able to sort by multiple fields.
My SQL looked something like this:
select col1, col2, col3 from t1 join (select col as col2 from t2 where name='data1') t2 on t1.id=t2.id join (select col as col3 from t2 where name='data1') t3 order by col2, col3 limit 5
This recursively broke the mysql engine, as I have actually got 100000 records in both tables.
I found this article: here on the problem, which was helpful, but didn't solve my problem: all the keys in the world didn't optimize the subqueries once there was more than one, and I need the data out of the subqueries to display in my table!
Finally after much trial and error I found creating a view was the answer. A view describing the two subqueries now has an execution plan with only one table scan, and additional columns can be added without much performance hit.
Full SQL now looks like:
CREATE VIEW v_col2 AS
SELECT * FROM t1 where name='Data2';
CREATE VIEW v_col3 AS
SELECT * FROM t2 where name='Data1';
SELECT col1,t2.data as col2,t3.data as col3 from t1 join v_col2 t2 on t1.id=t2.id join v_col3 t3 order by col2, col3 limit 5
The first two lines are only run if they don't exist already
Interestingly temporary tables didn't work quite as well (still wanted a full row scan - but I may have missed a key), but I didn't like them anyway as they are not dynamically updated.
How that helps someone! I have not tried a similar test in other DB engines, and am curious how ORACLE or MSSQL perform. SQL 2000 also had issues with recursively running joins in my experience, maybe 2005 has fixed it?
Comments