MySQL statement performance close to infinity

I am using MySQL for a client's project. I am not a fan of MySQL -- I much prefer PostgreSQL -- but the client asked that I use it and I am learning a lot in the process. Somethings in MySQL are great, for example the consistent use of SQL for all management, and somethings are bad, for example, performance and performance analysis tools.

For example. I have a table (Q) with 22,000,000 records that a Java process is working through in batches of 200,000. After each batch I need to delete the processed records. Since the Java process can have multiple instances a temporary table (T) is created and initialized with a batch of records and then the Java instance processes the records in the temporary table. At the end of the batch the processed records are removed from the table Q and the temporary table T is dropped. The delete query was

delete from q where id in ( select id from t )

The performance of this statement is close to infinity as far as I can tell. Don't use it. Instead use

delete from q using q, t where q.id = t.id

MySQL's explain does not compare to the PostgreSQL version. So, I don't know why the first statement is so bad and the second so good. It's a mystery. The good news is the client's project can now continue forward.

No comments: