Sure, I'll take a look at the paper for more in depth stuff. 

Just on the quick: 

Say I want to do:

"select * from bigtable where col1 = 'peter'l;"

Will an SQL index create:

"create index col1_index on bigtable (col1);"

be simply ignored by MonetDB?



On Fri, Mar 20, 2009 at 3:08 PM, Martin Kersten <Martin.Kersten@cwi.nl> wrote:
Lefteris wrote:
> On Fri, Mar 20, 2009 at 10:52 PM, Yue Sheng <yuesheng8@gmail.com> wrote:
>> Great. It works fine. Took a bit longer than just adding a constant
>> 1111111111 seconds though. (about 11 fold increase in time).
>> Should I bother with indexing? Or is MonetDB going to take care of all that?
>> Thx
>
> You should define a sorting on the columns that are more often used
> for select/join etc. I am not aware of the exact SQL commands but the
> SQL wizards on the list can help on this.
Sortedness is detected upon insert of individual tuples, but as always
there can be just one physical ordering for the table at large.
Everything beyond that point is for technical DBAs and programmers.

The indexing aspect of MonetDB has been documented in the various
science papers. To read the latest on that topic, we refer to
the cracking papers. See our science library.

>
>> On Fri, Mar 20, 2009 at 1:41 PM, Niels Nes <Niels.Nes@cwi.nl> wrote:
>>> On Fri, Mar 20, 2009 at 01:32:36PM -0700, Yue Sheng wrote:
>>>>    Great thanks!
>>>>
>>>>    So i just need to do
>>>>
>>>>    sql> select TIMESTAMP '1970-01-01 00:00:00' + interval col4
>>>>    second;
>>> probably a cast is the proper statement.
>>>
>>> Cast (col4 as interval second)
>>>
>>> Niels
>>>>    to add, for each row, the number of seconds in col4 to the
>>>>    epoch.
>>>>
>>>>    On Fri, Mar 20, 2009 at 11:46 AM, Niels Nes
>>>>    <[1]Niels.Nes@cwi.nl> wrote:
>>>>
>>>>    On Fri, Mar 20, 2009 at 11:32:54AM -0700, Yue Sheng wrote:
>>>>    >    There's no string involved. Just to make doubly clear, my
>>>>    >    "dates" are in Unix time format, so 32bit unsigned
>>>>    integer. For
>>>>    >    example: 1230648143. This is the number of second from
>>>>    epoch:
>>>>    >    1970-01-01 00:00:00
>>>>
>>>>      sql>select TIMESTAMP '1970-01-01 00:00:00' + interval '1'
>>>>      second;
>>>>      +----------------------------+
>>>>      | sql_add_single_value       |
>>>>      +============================+
>>>>      | 1970-01-01 00:00:01.000000 |
>>>>      +----------------------------+
>>>>      1 tuple
>>>>      sql>select TIMESTAMP '1970-01-01 00:00:00' + interval
>>>>      '1111111111' second;
>>>>      +----------------------------+
>>>>      | sql_add_single_value       |
>>>>      +============================+
>>>>      | 2005-03-18 01:58:31.000000 |
>>>>      +----------------------------+
>>>>      1 tuple
>>>>      Niels
>>>>      >
>>>>      >    reference: [1][2]http://en.wikipedia.org/wiki/Unix_time
>>>>
>>>>    >    On Fri, Mar 20, 2009 at 11:24 AM, Niels Nes
>>>>
>>>>    >    <[2][3]Niels.Nes@cwi.nl> wrote:
>>>>    >
>>>>    >    On Fri, Mar 20, 2009 at 11:16:07AM -0700, Yue Sheng wrote:
>>>>    >    >    MonetDB doesn't seem to support DATEADD function. I
>>>>    need
>>>>    >    it to
>>>>    >    >    convert Unix time on-the-fly (700milliion times for
>>>>    each
>>>>    >    >    query!) . Could anyone recommend a fast way to do
>>>>    this
>>>>    >    please?
>>>>    >    >    Thanks
>>>>    >
>>>>    >      DATEADD isn't a standard SQL function, so could you tell
>>>>    us
>>>>    >      the
>>>>    >      expected semantics?
>>>>    >      For string to date conversions you could load the file
>>>>    >      (src/sql/date.sql).
>>>>    >      Or run
>>>>    >      create function str_to_date(s string, format string)
>>>>    returns
>>>>    >      date
>>>>    >             external name mtime."str_to_date";
>>>>    >      This adds the function str_to_date, which needs a string
>>>>    >      column and
>>>>    >      a format string. It depends on strptime, so you could do
>>>>    a
>>>>    >      man
>>>>    >      strptime for details on the format string.
>>>>    >      Niels
>>>>    >      >
>>>>    >
>>>>    ------------------------------------------------------------
>>>>    >      ------------------
>>>>    >      > Apps built with the Adobe(R) Flex(R) framework and
>>>>    Flex
>>>>    >      Builder(TM) are
>>>>    >      > powering Web 2.0 with engaging, cross-platform
>>>>    >      capabilities. Quickly and
>>>>    >      > easily build your RIAs with Flex Builder, the
>>>>    >      Eclipse(TM)based development
>>>>    >      > software that enables intelligent coding and
>>>>    step-through
>>>>    >      debugging.
>>>>    >      > Download the free 60 day trial.
>>>>
>>>>      >      [3][4]http://p.sf.net/sfu/www-adobe-com
>>>>      >      > _______________________________________________
>>>>      >      > MonetDB-users mailing list
>>>>      >      > [4][5]MonetDB-users@lists.sourceforge.net
>>>>      >      >
>>>>      >
>>>>      [5][6]https://lists.sourceforge.net/lists/listinfo/monetdb-u
>>>>      ser
>>>>
>>>>    >      s
>>>>    >      --
>>>>    >      Niels Nes, Centre for Mathematics and Computer Science
>>>>    (CWI)
>>>>    >      Science Park 123, 1098 XG Amsterdam, The Netherlands
>>>>    >      room C0.02,  phone ++31 20 592-4098, fax ++31 20
>>>>    592-4312
>>>>
>>>>      >      url: [6][7]http://www.cwi.nl/~niels   e-mail:
>>>>      >      [7][8]Niels.Nes@cwi.nl
>>>>
>>>>    >
>>>>    ------------------------------------------------------------
>>>>    >      ------------------
>>>>    >      Apps built with the Adobe(R) Flex(R) framework and Flex
>>>>    >      Builder(TM) are
>>>>    >      powering Web 2.0 with engaging, cross-platform
>>>>    capabilities.
>>>>    >      Quickly and
>>>>    >      easily build your RIAs with Flex Builder, the
>>>>    >      Eclipse(TM)based development
>>>>    >      software that enables intelligent coding and
>>>>    step-through
>>>>    >      debugging.
>>>>    >      Download the free 60 day trial.
>>>>
>>>>      >      [8][9]http://p.sf.net/sfu/www-adobe-com
>>>>      >      _______________________________________________
>>>>      >      MonetDB-users mailing list
>>>>      >      [9][10]MonetDB-users@lists.sourceforge.net
>>>>      >
>>>>      [10][11]https://lists.sourceforge.net/lists/listinfo/monetdb
>>>>      -use
>>>>      >      rs
>>>>      >
>>>>      > References
>>>>      >
>>>>      >    1. [12]http://en.wikipedia.org/wiki/Unix_time
>>>>      >    2. mailto:[13]Niels.Nes@cwi.nl
>>>>
>>>>    >    3. [14]http://p.sf.net/sfu/www-adobe-com
>>>>
>>>>      >    4. mailto:[15]MonetDB-users@lists.sourceforge.net
>>>>      >    5.
>>>>      [16]https://lists.sourceforge.net/lists/listinfo/monetdb-use
>>>>      rs
>>>>      >    6. [17]http://www.cwi.nl/~niels
>>>>      >    7. mailto:[18]Niels.Nes@cwi.nl
>>>>
>>>>    >    8. [19]http://p.sf.net/sfu/www-adobe-com
>>>>
>>>>      >    9. mailto:[20]MonetDB-users@lists.sourceforge.net
>>>>      >   10.
>>>>      [21]https://lists.sourceforge.net/lists/listinfo/monetdb-use
>>>>      rs
>>>>
>>>>    >
>>>>    ---------------------------------------------------------------
>>>>    ---------------
>>>>    > Apps built with the Adobe(R) Flex(R) framework and Flex
>>>>    Builder(TM) are
>>>>    > powering Web 2.0 with engaging, cross-platform capabilities.
>>>>    Quickly and
>>>>    > easily build your RIAs with Flex Builder, the
>>>>    Eclipse(TM)based development
>>>>    > software that enables intelligent coding and step-through
>>>>    debugging.
>>>>    > Download the free 60 day trial.
>>>>    [22]http://p.sf.net/sfu/www-adobe-com
>>>>    > _______________________________________________
>>>>    > MonetDB-users mailing list
>>>>    > [23]MonetDB-users@lists.sourceforge.net
>>>>    >
>>>>    [24]https://lists.sourceforge.net/lists/listinfo/monetdb-users
>>>>    --
>>>>    Niels Nes, Centre for Mathematics and Computer Science (CWI)
>>>>    Science Park 123, 1098 XG Amsterdam, The Netherlands
>>>>    room C0.02,  phone ++31 20 592-4098, fax ++31 20 592-4312
>>>>    url: [25]http://www.cwi.nl/~niels   e-mail:
>>>>    [26]Niels.Nes@cwi.nl
>>>>    ---------------------------------------------------------------
>>>>    ---------------
>>>>    Apps built with the Adobe(R) Flex(R) framework and Flex
>>>>    Builder(TM) are
>>>>    powering Web 2.0 with engaging, cross-platform capabilities.
>>>>    Quickly and
>>>>    easily build your RIAs with Flex Builder, the Eclipse(TM)based
>>>>    development
>>>>    software that enables intelligent coding and step-through
>>>>    debugging.
>>>>    Download the free 60 day trial.
>>>>    [27]http://p.sf.net/sfu/www-adobe-com
>>>>    _______________________________________________
>>>>    MonetDB-users mailing list
>>>>    [28]MonetDB-users@lists.sourceforge.net
>>>>    [29]https://lists.sourceforge.net/lists/listinfo/monetdb-users
>>>>
>>>> References
>>>>
>>>>    1. mailto:Niels.Nes@cwi.nl
>>>>    2. http://en.wikipedia.org/wiki/Unix_time
>>>>    3. mailto:Niels.Nes@cwi.nl
>>>>    4. http://p.sf.net/sfu/www-adobe-com
>>>>    5. mailto:MonetDB-users@lists.sourceforge.net
>>>>    6. https://lists.sourceforge.net/lists/listinfo/monetdb-user
>>>>    7. http://www.cwi.nl/~niels
>>>>    8. mailto:Niels.Nes@cwi.nl
>>>>    9. http://p.sf.net/sfu/www-adobe-com
>>>>   10. mailto:MonetDB-users@lists.sourceforge.net
>>>>   11. https://lists.sourceforge.net/lists/listinfo/monetdb-use
>>>>   12. http://en.wikipedia.org/wiki/Unix_time
>>>>   13. mailto:Niels.Nes@cwi.nl
>>>>   14. http://p.sf.net/sfu/www-adobe-com
>>>>   15. mailto:MonetDB-users@lists.sourceforge.net
>>>>   16. https://lists.sourceforge.net/lists/listinfo/monetdb-users
>>>>   17. http://www.cwi.nl/~niels
>>>>   18. mailto:Niels.Nes@cwi.nl
>>>>   19. http://p.sf.net/sfu/www-adobe-com
>>>>   20. mailto:MonetDB-users@lists.sourceforge.net
>>>>   21. https://lists.sourceforge.net/lists/listinfo/monetdb-users
>>>>   22. http://p.sf.net/sfu/www-adobe-com
>>>>   23. mailto:MonetDB-users@lists.sourceforge.net
>>>>   24. https://lists.sourceforge.net/lists/listinfo/monetdb-users
>>>>   25. http://www.cwi.nl/~niels
>>>>   26. mailto:Niels.Nes@cwi.nl
>>>>   27. http://p.sf.net/sfu/www-adobe-com
>>>>   28. mailto:MonetDB-users@lists.sourceforge.net
>>>>   29. https://lists.sourceforge.net/lists/listinfo/monetdb-users
>>>> ------------------------------------------------------------------------------
>>>> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
>>>> powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
>>>> easily build your RIAs with Flex Builder, the Eclipse(TM)based
>>>> development
>>>> software that enables intelligent coding and step-through debugging.
>>>> Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
>>>> _______________________________________________
>>>> MonetDB-users mailing list
>>>> MonetDB-users@lists.sourceforge.net
>>>> https://lists.sourceforge.net/lists/listinfo/monetdb-users
>>>
>>> --
>>> Niels Nes, Centre for Mathematics and Computer Science (CWI)
>>> Science Park 123, 1098 XG Amsterdam, The Netherlands
>>> room C0.02,  phone ++31 20 592-4098, fax ++31 20 592-4312
>>> url: http://www.cwi.nl/~niels   e-mail: Niels.Nes@cwi.nl
>>>
>>>
>>> ------------------------------------------------------------------------------
>>> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
>>> powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
>>> easily build your RIAs with Flex Builder, the Eclipse(TM)based development
>>> software that enables intelligent coding and step-through debugging.
>>> Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
>>> _______________________________________________
>>> MonetDB-users mailing list
>>> MonetDB-users@lists.sourceforge.net
>>> https://lists.sourceforge.net/lists/listinfo/monetdb-users
>>
>> ------------------------------------------------------------------------------
>> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
>> powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
>> easily build your RIAs with Flex Builder, the Eclipse(TM)based development
>> software that enables intelligent coding and step-through debugging.
>> Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
>> _______________________________________________
>> MonetDB-users mailing list
>> MonetDB-users@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/monetdb-users
>>
>>
>
> ------------------------------------------------------------------------------
> Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
> powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
> easily build your RIAs with Flex Builder, the Eclipse(TM)based development
> software that enables intelligent coding and step-through debugging.
> Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
> _______________________________________________
> MonetDB-users mailing list
> MonetDB-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/monetdb-users


------------------------------------------------------------------------------
Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are
powering Web 2.0 with engaging, cross-platform capabilities. Quickly and
easily build your RIAs with Flex Builder, the Eclipse(TM)based development
software that enables intelligent coding and step-through debugging.
Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com
_______________________________________________
MonetDB-users mailing list
MonetDB-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/monetdb-users