On Wed, May 12, 2010 at 3:20 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
venu madhav <
venutaurus539@gmail.com> wrote:
> When I try to get the last twenty records from the database, it
> takes around 10-15 mins to complete the operation.
select e.cid, timestamp, s.sig_class, s.sig_priority, s.sig_name,
e.sniff_ip, e.sniff_channel, s.sig_config, e.wifi_addr_1,
e.wifi_addr_2, e.view_status, bssid
FROM event e,
signature s
WHERE s.sig_id = e.signature
AND e.timestamp >= '1270449180'
AND e.timestamp < '1273473180'
ORDER BY
e.cid DESC,
e.cid DESC
limit 21
offset 10539780
;
Why the timestamp range, the order by, the limit, *and* the offset?
On the face of it, that seems a bit confused. Not to mention that
your ORDER BY has the same column twice.
[Venu] The second column acts as a secondary key for sorting if the primary sorting key is a different column. For this query both of them are same. This query is part of an application which allows user to select time ranges and retrieve the data in that interval. Hence the time stamp. To have it in some particular order we're doing order by. If the records are more in the interval, we display in sets of 20/30 etc. The user also has the option to browse through any of those records hence the limit and offset.
Perhaps that OFFSET is not needed? It is telling PostgreSQL that
whatever results are generated based on the rest of the query, read
through and ignore the first ten and a half million. Since you said
you had about ten million rows, you wanted the last 20, and the
ORDER by is DESCending, you're probably not going to get what you
want.
What, exactly, *is* it you want again?
[Venu] As explain above this query is part of the application where user wishes to see the records from the database between any start and end times. They get rendered as a HTML page with pagination links to traverse through the data. The user has option to go to any set of records. When the user asks for the last set of 20 records, this query gets executed.
Hope it is clear now. Please let me know if you need any further info.
Thank you,
Venu
-Kevin