Counting all records but selecting only a few

htmanning has asked for the wisdom of the Perl Monks concerning the following question:

I apologize if this isn't strictly Perl related. I have a database with over 100,000 records. It is getting slow. I currently select all records but display only 50 per page. If I limit the search to the last 500, it is fast but I can't count all the records. How can I count all records, but only grab the last 500 for speed? It seems like it might require 2 database calls. Can I accomplish this in 1 call to the database? I'm using the following:

SELECT * from $log_table order by sticky desc, dateadded2 desc, ID DES
+C limit 500";
[download]

If I leave out the "limit 500" it shows me that it found 100,000 records but shows me only 1-50.

What I would like to have happen is the page say, Found 500 of 100,000 records. Displaying 1-50.

Comment on Counting all records but selecting only a few Download Code

Replies are listed 'Best First'.
Re: Counting all records but selecting only a few by NetWallah (Canon) on Apr 05, 2018 at 05:54 UTC
`SELECT COUNT() from <table>;` [download] That query is typically VERY fast. I suggest you do that first, to get the count, then SELECT your last 500. Memory fault -- brain fried*	[reply] [d/l]
Re: Counting all records but selecting only a few by Marshall (Canon) on Apr 05, 2018 at 04:11 UTC
Please show your Perl code. SELECT * from $log_table order by 'sticky desc', dateadded2 will result in all records from log_table, sorted by an order. If log_table is only 100,000 entries, this is very fast. Probably so fast that you can't blink that fast.	[reply]
Re: Counting all records but selecting only a few by Anonymous Monk on Apr 05, 2018 at 11:45 UTC
Use two queries and don't worry about it.	[reply]