Tekker Blog

I was working on a project for a client today and ran into a strange issue.  I have 3 main tables in myisam storage format that work is being performed on.

TableA: ID (int), Title (varchar(255)). This table has under 100 records.

TableB: ID (int), TableAID (int), Title (varchar(255)). This table has about 35,000 records.

TableC: ID (int), TableBID (int), Data1 (varchar(255)), TextField1 (text). This table has about 1,000,000 records.

I had added some new rows to TableA and the work performed on that row would generate rows in TableB.  The work preformed on TableB would generate rows in TableC.  I wanted to see all the data in TableC that had been added as a result of adding rows to TableA so I used a query similar to the following:

select * from TableC where TableBID in (
     select ID from TableB where TableAID in (
          select ID from TableA where ID > 30
     )
)

This query took over 20 minutes to complete.  So, I took the subqueries and dumped the results, 81 records, into a temp table.  Then ran:

select * from TableC where TableBID in (select id from temptable)

And the query finished in under a second.  What gives?  It’s the same damn query. Does mysql rerun the subqueries against each record in TableC?  If so, this is terrible.  It should, at minimum, cache the results of each subquery and to further enhance performance, it should actually process the innermost query, cache the results, run the next subquery out and cache those results and then on the final query (the results I want to see) and just say “where in ‘cache results’”.  Maybe I am just naive in the ways of MySQL, but plenty of other database platforms don’t act like this.

Anyone have similar experiences? If so, please leave a comment.