Re: Index to help ordering?

On 17 January 2014 20:47, Daniel Staal <DStaal(at)usa(dot)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.)

Hi Daniel,

ppid is not unique.
I am often not pulling in all of the day. Far from it.

Basically I have about 75,000 people (ppid) and a record for each
minute of the day for each person. So 24 hours x 60 minutes x 75,000
people.

I 'normally' want to just view one person's 'day' at a time. So I
might do something like this:

SELECT ppid, point_time, mode, concentration FROM table WHERE ppid =
'43' ORDER BY point_time;

Does this revise how you think I should go about speeding up this query?

Thanks for your help,

James