On Sat, Jan 18, 2014 at 4:47 AM, Daniel Staal <DStaal@usa.net> wrote:
--As of January 17, 2014 4:41:26 PM +0000, James David Smith is alleged to have said:
I have a table of some 100m rows of data. There are 5 columns of data. When I want to look at this data I typically want to sort it by ppid (numeric) an then by time (timestamp). This simple select can often take 15-20 seconds. Would adding an index to these two columns make it quicker? What is the best way to achieve increased speed for this common select query?
--As for the rest, it is mine.
An index is likely to make it faster, although I wouldn't quite want to guarantee it. (I assume you are pulling in all the data? Otherwise an index on your *conditions* might make sense. Sorting the data likely takes less time than retrieving all of it.) Luckily enough it's easy to just try it and check.
Since we are working on that one query in specific, I'd probably create an index on those two columns, in that order: `CREATE INDEX ixd_name ON table (ppid, time)`. I'm of course assuming that ppid's aren't unique.
But again, if you are retrieving the entire table this may not help - what would help more is to limit the number of records you are retrieving and put an index on that condition. (Of course, if you are using LIMIT, then the index will definitely help.)
Daniel T. Staal
--------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
In addition to what has been already said, CLUSTERing the table by given index could be of some more help.