Tag Info

New answers tagged

1

Please provide some sizes. For example, how big would BIG be when it is time to remove the rows? How big are the other tables? MEMORY is not necessarily better than InnoDB. It may be slower because of table vs row locking. It may interfere with overall performance because of taking RAM away from the buffer_pool for BIG, thereby slowing down other ...


1

As you are using SQL Server 2014, you could have a look at In-memory OLTP tables (nee Hekaton). They are suitable to write-intensive applications and there is a published pattern called Shock Absorber. Start here.


1

I don't expect this to be better than the already provided alternatives but it may be worth testing and adding to the options: with t as ( select event_id, user_id, event_type, ts, row_number() over w as rn from events window w as (partition by user_id, event_type order by ts) ) select t.event_id, t.user_id, ...


3

I accepted @Erwin's answer but here are the benchmarks on generated data (10000 rows, best of 5 executions) using the corrected queries. I run it with the multi-colmun index. As expected, queries 1 (26.324 ms) and 2 (23.264 ms) are rather similar in terms of performance while query 3 is the slowest (32.775 ms). CREATE INDEX events_fast_idx ON events ...


4

Assuming this sanitized table definition CREATE TABLE events ( event_id serial PRIMARY KEY , user_id int , event_type int , ts timestamp -- don't use reserved word as identifier ); Your comparison seems unfair, the first query has ORDER BY event_id, but the second hasn't. The EXPLAIN output does not fit the first query (no sort step). Be ...



Top 50 recent answers are included