Re: Index to help ordering?

On Sat, Jan 18, 2014 at 4:47 AM, 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.)
>
> 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.

Best Regards,
*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M : *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

[image: email patch]

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).