I did an experiment where I select for a specific randomly chosen binB
value with and without an index on binB:
explain analyze SELECT binA, value FROM mytable WHERE binB =
X'00234d33def71358d61895649e6ab3912c0448214f'
*With index:*
SELECT
BINA,
VALUE
FROM PUBLIC.MYTABLE
/* PUBLIC.MYTABLEINDEX: BINB =
X'00234d33def71358d61895649e6ab3912c0448214f' */
/* scanCount: 2 */
WHERE BINB = X'00234d33def71358d61895649e6ab3912c0448214f'
/*
total: 6
UNSPENT.MYTABLEINDEX read: 4 (66%)
UNSPENT.MYTABLE_DATA read: 2 (33%)
*/
*Without index:*
SELECT
BINA,
VALUE
FROM PUBLIC.MYTABLE
/* PUBLIC.MYTABLE.tableScan */
/* scanCount: 8237239 */
WHERE BINB = X'00234d33def71358d61895649e6ab3912c0448214f'
/*
total: 428969
UNSPENT.MYTABLE_DATA read: 428969 (100%)
*/
The result is pretty clear. The index is of tremendous help for this kind
of operation which is paramount for my setup.
On Thu, Feb 6, 2014 at 9:11 AM, Jan Møller <[email protected]> wrote:
> I failed to mention that I am using an SSD.
> Apart from wanting to make a pass over all rows during program start I am
> doing continuous random selects on binA (for getting zero or one row) and
> binB (for getting zero, one or more rows) at runtime (plus random
> inserts/deletes). So I figured that an index would speed up those selects.
>
> So, to make certain I understand: Was your index comment only meant for
> the "read all rows during startup" comment, or do you think that there is
> generally no use in indexes on random data?
>
> In that case I might as well get rid of them and save some space.
>
> Thanks for your help.
>
>
> On Wed, Feb 5, 2014 at 8:02 PM, Thomas Mueller <
> [email protected]> wrote:
>
>> Hi,
>>
>> > binA and binB contain basically random bytes, but again this is
>> unrelated to my question
>>
>> Well, this is actually relevant. You have two indexes (one primary key,
>> and a secondary key) on randomly distributed data. There is no way to make
>> it really fast, unless it fits in memory. Having to use an index on
>> randomly distributed data is a "cache killer". All you can really do in
>> this case is try to load everything in memory, or use a solid state disk.
>>
>> Because of that, I would avoid an index on randomly distributed data (no
>> matter what database is storage system you use), unless you know in advance
>> that the number of entries will never grow beyond a certain size, or unless
>> the amount of data you store is so big that the lookup cost (at most 200
>> per second for a regular hard disk) is lower than the amount of data. That
>> means, each entry is about 2 MB in size or larger.
>>
>> See also the documentation at
>> http://h2database.com/html/datatypes.html#uuid_type
>>
>> Regards,
>> Thomas
>>
>>
>>
>>
>>
>> On Wed, Feb 5, 2014 at 11:04 AM, Noel Grandin <[email protected]>wrote:
>>
>>> You could so something like:
>>>
>>> SELECT binA, binB FROM myTable ORDER BY binA LIMIT 10000
>>>
>>> and then store the last binA value you receive and go:
>>>
>>> SELECT binA, binB FROM myTable WHERE binA >
>>> lastBinAValueFromPreviousSelect ORDER BY binA LIMIT 10000
>>>
>>> and repeat that until you have processed all the data.
>>>
>>> Which should be reasonably snappy because it can use the primary index
>>> to locate the data efficiently.
>>>
>>>
>>> On 2014-02-05 11:43, Jan Møller wrote:
>>>
>>>>
>>>> On startup I wish to read through it all to generate a graph, so
>>>> basically: read next element, do something, rinse and
>>>> repeat
>>>>
>>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "H2 Database" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to [email protected].
>>>
>>> To post to this group, send email to [email protected].
>>> Visit this group at http://groups.google.com/group/h2-database.
>>> For more options, visit https://groups.google.com/groups/opt_out.
>>>
>>
>> --
>> You received this message because you are subscribed to a topic in the
>> Google Groups "H2 Database" group.
>> To unsubscribe from this topic, visit
>> https://groups.google.com/d/topic/h2-database/KhbJSGdhLcM/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to
>> [email protected].
>> To post to this group, send email to [email protected].
>> Visit this group at http://groups.google.com/group/h2-database.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
