Hello precious and brilliant minds,
I would like to know if there's a better way (syntactical or logical) to achieve a better performance for the scenario below:
Let's say we have any kind of query with a LIMIT and an OFFSET (used to limit/offset rows displayed on the web page). For example:
QUERY1:
SELECT col1, col2, col3 FROM tableA WHERE col1='qwerty' LIMIT 25 OFFSET 0;
Now I would like to run the same query WITHOUT LIMIT and OFFSET to obtain the total count of the rows:
QUERY2:
SELECT COUNT(*) INTO row_count FROM tableA WHERE col1='qwerty';
I use QUERY1 to display the actual data and QUERY2 to calculate the number of 'pages' to display web page pagination (like 1 - 2 - 3 --- 10).
To achive this I either run the two queries separately or combine the two queries by placing the second one as a subselect:
SELECT col1, col2, col3, (SELECT COUNT(*) FROM tableA WHERE col1='qwerty') AS row_count FROM tableA WHERE col1 = 'qwerty' LIMIT 25 OFFSET 0;
This way I'm able to collect both the data and the total row count. The problems:
1) This method requires me to type the 'same' query twice, one with LIMIT and OFFSET, the other one with COUNT(*) but without LIMIT and OFFSET.
2) In real cases, our queries are really complex joining 8 to 15 tables with several WHERE conditions. This double querying poses significant load on the server and is prone to typos.
3) I've also learned that COUNT(*) is slow due to sequential scans. In my scenario this is unavoidable since I require it, but there may be a trick to reduce its overall cost.
The only alternative solution I've come up with so far is to save row counts into the table itself using triggers. Is this reasonable or not?
I'm in search for a new method / syntax to combine these two queries and I welcome your suggestions.
Thanks in advance.
Volkan Varol
Ajansburada
http://www.ajansburada.com
Antalya, Turkey
T. +90 242 316 25 24
F. +90 242 316 25 52
