Upgrade to Lion -- why Valentina has columnar format ...

Ruslan Zasukhin ruslan_zasukhin at valentina-db.com
Thu May 31 15:46:56 EDT 2012


On 5/31/12 9:35 PM, "Bob Sneidar" <bobs at twft.com> wrote:

> Yes, but it is my understanding of SQL that a query on a non-indexed column
> will create a temporary index in mySQL and maybe others.

I do not think so ...

Look. To build index db engine need read all values of column yes?
and yet build the whole index file, doing a lots of searches where to insert
next value.

Much simpler just to scan column and get answer ...

Temporary index can be used may be for sorting ...


> I might be wrong 
> about that. But Foxpro does not do that. In fact if you set a filter or seek
> using a FOR statement that has no indexed columns or has some non-indexed
> columns, Foxpro will proceed at a reduced speed without any temporary
> indexing. 

Right. 

The same do Valentina. If column is not indexed, then we do just
   "scan of column" operation.
 
And since this is VERTIAL storage, this means Valentina need touch only e.g.
4MB (if table have million records and column is ULONG).

Row-based dbs will need load the whole table, to extract values of this
single column ...


> Of course, you could index all columns that you will ever search on, but this
> is not always the best, because depending on the application, many columns may
> only occasionally be searched, and the overhead of updating all those indexes
> becomes counter-productive in terms of speed overall.

Right.

Developer need index only the most hot fields that are used in searches,
joins and sorting ...

> So the principle of indexing columns that you search often, as you stated is
> the real life standard for indexing imho.
> 
> Bob
> 
> 
> On May 31, 2012, at 11:19 AM, Ruslan Zasukhin wrote:
> 
>> On 5/31/12 8:14 PM, "Bob Sneidar" <bobs at twft.com> wrote:
>> 
>>> Foxpro does, but it's slow.
>> 
>> You mean that foxpro can do
>>    search on column that is not indexed ?
>> 
>> Well, of course each db engine can do this.  :-)
>> And Valentina, and Light, and mySQL, ...
>> And yes it is much slower than indexed search,


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]






More information about the Use-livecode mailing list