List:General Discussion« Previous MessageNext Message »
From:Andrew Braithwaite Date:April 27 2009 9:23am
Subject:RE: Index time columns?
View as plain text  
Hi,

If you have that date column in your where clause for example:

SELECT ...... FROM ..... WHERE tstamp > NOW() - INTERVAL 1 WEEK;

Then it's essential to index that column to speed up a table with lots of data.

On a table with many rows, an index on a timestamp column is invaluable.  However, I should point out that having an index on a column does add a small performance overhead to inserts and updates so if you're not going to use it in your where clauses then there's not much point in adding it.

Cheers,

Andrew

-----Original Message-----
From: Cantwell, Bryan [mailto:bcantwell@stripped] 
Sent: 24 April 2009 22:56
To: mysql@stripped
Subject: Index time columns?

I have a debate with someone about indexes on time columns.
So I ask the community, should you put an index on a timestamp column of a table? 
Thanks...
Thread
Oracle , what else ?Gilles MISSONNIER21 Apr
  • Re: Oracle , what else ?Simon Connah21 Apr
  • Re: Oracle , what else ?Martijn Tonies21 Apr
    • Re: Oracle , what else ?Joshua D. Drake22 Apr
      • Re: Oracle , what else ?Glyn Astill23 Apr
        • Re: Oracle , what else ?Joshua D. Drake23 Apr
  • Re: Oracle , what else ?mos21 Apr
    • Re: Oracle , what else ?Andy Shellam21 Apr
    • Re: Oracle , what else ?Yves Goergen23 Apr
    • RE: Oracle , what else ?Gabriel - IP Guys23 Apr
      • RE: Oracle , what else ?Janek Bogucki28 Apr
        • RE: Oracle , what else ?John Daisley28 Apr
          • RE: Oracle , what else ?mos28 Apr
      • RE: Oracle , what else ?Janek Bogucki28 Apr
  • Re: Oracle , what else ?Martijn Tonies24 Apr
    • Re: Oracle , what else ?Joshua D. Drake24 Apr
    • Re: Oracle , what else ?mos24 Apr
Re: Oracle , what else ?John Daisley22 Apr
  • Re: Oracle , what else ?Arthur Fuller22 Apr
    • Re: Oracle , what else ?NĂ©stor22 Apr
      • Re: Oracle , what else ?mos22 Apr
        • Re: Oracle , what else ?Krishna Chandra Prajapati22 Apr
  • Re: Oracle , what else ?Martijn Tonies22 Apr
RE: Oracle , what else ?Joshua D. Drake24 Apr
  • Re: Oracle , what else ?David Sparks24 Apr
    • Re: Oracle , what else ?Glyn Astill24 Apr
      • Re: Oracle , what else ?David Sparks24 Apr
        • Re: Oracle , what else ?Glyn Astill24 Apr
          • Index time columns?Bryan Cantwell24 Apr
            • RE: Index time columns?Andrew Braithwaite27 Apr
        • Re: Oracle , what else ?Joshua D. Drake27 Apr
      • Re: Oracle , what else ?Joshua D. Drake27 Apr
    • Re: Oracle , what else ?Joshua D. Drake27 Apr