Tag Info

New answers tagged

2

Instead of doing TRUNCATE TABLE (which locks up any connections accessing the table), try making an empty copy of the table, swapping it in, and dropping the old table. EXAMPLE Suppose the table is called mydb.mytable. Do it like this USE mydb CREATE TABLE mytable_new LIKE mytable; ALTER TABLE mytable RENAME mytable_old; ALTER TABLE mytable_new RENAME ...


1

Although TRUNCATE TABLE is definitely faster than DELETE FROM I would stick to deleting the records in small chunks. The TRUNCATE TABLE sometimes can be still slow because a lot of stuff is going on behind scenes: it has to grab exclusive lock on the dictionary, it still has to delete ibd file and re-create one, it has to evict pages from the buffer pool. ...


1

Use TRUNCATE TABLE (that will empty the table in the fastest way possible, by droping it and recreate it in a non-rollable-back way. If that takes too much time for you (can happen in older versions of mysql using innodb_file_per_table), you can run it independently on master and each slave with SET sql_log_bin = 0; The underlying bug is probably this ...


1

I found figured out an answer that works pretty well, though if you have a lot of users it may take some time to execute. I have yet to do any time testing with it. It also assumes you have an id field that uniquely identifies each score. The way it works is in the inner query it finds up to the 10 most recent scores. Then deletes everything not returned ...


0

here's a quick and ugly query that make the job : with nsp_oid as ( select oid from pg_namespace where nspname='mySchema' ), dep_oid as ( select p.objid as oid from pg_depend p join nsp_oid n on refobjid = n.oid ), rew_oid as( select p.objid as oid from pg_depend p join dep_oid d on p.refobjid = d.oid where ...


0

You can use pg_depend table to resolve that :  select objid::regclass from pg_depend where refobjid = (select oid from pg_namespace where nspname='mySchema');



Top 50 recent answers are included