serverside count query so slow. any idea?

casuist

The table has more than 2000k rows.
Server side load data is very slow.
Neither pages nor movement is very slow.

However, querying the data is fast. About 500 ms.
Count query speed is about 5 seconds.

So I tried to write count to database on insert, delete.
Is that data available?

Or is there another good way?

casuist

how stop count query on move page??

allan

Wow - that is slow. What database engine are you using?

Keeping a count using a trigger works great for most web-applications, which tend to be more read heavy. If you were updated the db every second or so with new data, then you might want to not use such a trigger, but is most cases, that's a good approach (although it can fall apart if you need to add a condition to the query).

I'd suggest doing an EXPLAIN ANALYZE on the COUNT query to see what the db says about it.

Regards,
Allan