Tag Info

New answers tagged

0

There is a trick you can use to filter aggregates: select --... , sum(case when CONDITION THEN 1 ELSE 0 END) AS FilteredCount , sum(case when CONDITION THEN SomeCol ELSE 0 END) AS FilteredSumOfSomeCol If you can fit your aggregates into this scheme you can avoid the need for repeated query parts like in the current code. It looks like you can use this ...


0

You could reindex the table and even shrink the table. However, if you want to delay such disk-based maintenance, you should, at the very least, recompute the index statistics. Without recomputing the index statistics, the MySQL Query Optimizer may make bad choices for query EXPLAIN plans. This could adversely affect SELECTs if the statistics for nonexist ...


1

When there is a lot on insert and delete operations on a table, the table will be "fragmented", and the statistics that are used to decide the execution plan of a query will not be accurate. So YES, it is recommended to optimize the table from time to time (Not necessarily after each delete, but when a significant changes happen) hth


-1

If there is no update and you are deleting old data, you might think of creating daily tables with date on its name. You can create a view to query them and you can easily drop the old table.


1

I am sure that you can improve performance using indices on foreign keys i.e put an index on the accountholderid when you use it as foreign key. It's the place where you would usually put them. And you might put index on LastModificationTImestamp with desc order CREATE INDEX accountholder_LastModificationTimestamp_idx ON ...


0

By default, innodb_thread_concurrency is 0. That's actually the best setting. It means infinite concurrency. It allows the InnoDB storage engine to decide the best number of concurrency tickets to launch and address. Setting it to a nonzero value actually can throttle InnoDB or throttle the OS if not set properly. I have written many posts in the DBA ...


1

MattU's comment above about caching was enough to help me find a solution. Whilst reading about those settings mentioned, I came across innodb-flush-log-at-trx-commit which was set to 1 but I've now set to 2. There are a couple of tables which we're using like a queue (refactoring this into a different architecture more suited is on our TODO list) and thus ...



Top 50 recent answers are included