StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Try in your deletion thread to first load the IDs of the records to be deleted and then delete one at a time, committing after each delete. </p> <p>If you run the thread that does the huge delete once a day and it takes 3 minutes, you can split it to smaller transactions that delete a small number of records, and still manage to get it done fast enough.</p> <p>A better solution :</p> <p>First of all. Any solution you try must be tested prior to deployment in production. Especially a solution suggested by some random person on some random web site.</p> <p>Now, here's the solution I suggest (making some assumptions regarding your table structure and indices, since you didn't specify them):</p> <ol> <li><p>Alter your table. It's not recommended to have a primary key of multiple columns in InnoDB, especially in large tables (since the primary key is included automatically in any other indices). See the answer to <a href="https://stackoverflow.com/questions/7354271/mysql-should-i-use-multi-column-primary-keys-on-every-child-table">this question</a> for more reasons. You should add some unique RecordID column as primary key (I'd recommend a long identifier, or BIGINT in MySQL).</p></li> <li><p>Select the rows for deletion - execute "SELECT RecordID FROM YourTable where ServerTimeMillis < ?".</p></li> <li><p>Commit (to release the lock on the ServerTimeMillis index, which I assume you have, quickly)</p></li> <li><p>For each RecordID, execute "DELETE FROM YourTable WHERE RecordID = ?"</p></li> <li><p>Commit after each record or after each X records (I'm not sure whether that would make much difference). Perhaps even one Commit at the end of the DELETE commands will suffice, since with my suggested new logic, only the deleted rows should be locked.</p></li> </ol> <p>As for changing the isolation level. I don't think you have to do it. I can't suggest whether you can do it or not, since I don't know the logic of your server, and how it will be affected by such a change.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload