Deleting millions of rows in MySQL
DELETE FROM `table` WHERE (whatever criteria) ORDER BY `id` LIMIT 1000 Wash, rinse, repeat until zero rows affected. Maybe in a script that sleeps for a second or three between iterations.
DELETE FROM `table` WHERE (whatever criteria) ORDER BY `id` LIMIT 1000 Wash, rinse, repeat until zero rows affected. Maybe in a script that sleeps for a second or three between iterations.
MongoDB concatenates the compound key in some way and uses it as the key in a BTree. When finding single items – The order of the nodes in the tree is irrelevant. If you are returning a range of nodes – The elements close to each other will be down the same branches of the … Read more
I would propose a clean version based on DISTINCT ON (see docs): SELECT DISTINCT ON (usr_id) time_stamp, lives_remaining, usr_id, trans_id FROM lives ORDER BY usr_id, time_stamp DESC, trans_id DESC;
For inner joins, a single query makes sense, since you only get matching rows. For left joins, multiple queries is much better… look at the following benchmark I did: Single query with 5 Joins query: 8.074508 seconds result size: 2268000 5 queries in a row combined query time: 0.00262 seconds result size: 165 (6 + … Read more
You should definitely spend some time reading up on indexing, there’s a lot written about it, and it’s important to understand what’s going on. Broadly speaking, an index imposes an ordering on the rows of a table. For simplicity’s sake, imagine a table is just a big CSV file. Whenever a row is inserted, it’s … Read more
It’s better to use either of the following: — Method 1. SELECT 1 FROM table_name WHERE unique_key = value; — Method 2. SELECT COUNT(1) FROM table_name WHERE unique_key = value; The first alternative should give you no result or one result, the second count should be zero or one. How old is the documentation you’re … Read more
If you are trying to improve performance, you could try creating a persisted computed column on either a hash or concatenated value of the two columns. Once it is persisted, provided the column is deterministic and you are using “sane” database settings, it can be indexed and / or statistics can be created on it. … Read more