Re: [sqlite] Performing a SELECT on an enormous database...

[Ross Altman]

Oh, I see! Thanks for the clarification, Simon! I wasn't aware that there
was a command for indexing. That should definitely help a lot.

On Sat, Oct 25, 2014 at 4:32 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 25 Oct 2014, at 9:07pm, Ross Altman <altman...@husky.neu.edu> wrote:
>
> > Thanks for all the responses. The small integer column H11 comes before
> the
> > large string column NVERTS, so doesn't that mean SQLite is only loading
> the
> > minimum required while filtering? If that's the case then I don't
> > understand why it's taking up to 15 minutes to load.
>
> The searching is taking the time.  Because, as a number of other people
> have pointed out, there is no index on the H11 column, so SQLite has to
> look at every row in the table to see whether it qualifies for your
> SELECT.  Create an index by doing something like
>
> CREATE INDEX ToricCY_H11 ON ToricCY (H11)
>
> then do as many SELECTs as you want.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performing a SELECT on an enormous database...

[Ross Altman]

Thanks for all the responses. The small integer column H11 comes before the
large string column NVERTS, so doesn't that mean SQLite is only loading the
minimum required while filtering? If that's the case then I don't
understand why it's taking up to 15 minutes to load.

Also, yes this database is local, and not being read over a network.

Best,
Ross

On Sat, Oct 25, 2014 at 9:15 AM, Richard Hipp  wrote:

> On Sat, Oct 25, 2014 at 9:14 AM, Stephen Chrzanowski 
> wrote:
>
> > Ahh.. Thanks Richard.  So if you were to have blobs live at the front of
> > the row, it'll have to read through that blob to get that byte in the
> next
> > field, correct?
> >
> >
> Correct.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performing a SELECT on an enormous database...

[Ross Altman]

Hi guys,

I'm currently working with a pretty gigantic database (116 Gb at the
moment, and growing). Performing a simple SELECT routine with one filter
takes between 7 and 15 minutes, which is starting to become a problem. The
command I'm using is the following:

SELECT NVERTS FROM ToricCY WHERE H11=2;

Pretty basic. I just want the NVERTS column of the table ToricCY where
another column labeled H11 is 2, nothing fancy. Because of the huge size of
the database, I would expect it to take a while, but I was wondering if you
guys have any recommendations on how to speed it up (if possible).

Also, because this problem will only get worse as the database grows, I'm
looking for alternative approaches to storing this data. Does anyone have
experience working with databases this big?

Thanks!

Best,
Ross
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Ross Altman]

Yeah, that's actually a really good point. Oh well, I guess I'll just have
to hope that people decide to use the database responsibly... haha

Best,
Ross

On Tue, Oct 14, 2014 at 2:57 PM, Jungle Boogie <jungleboog...@gmail.com>
wrote:

> Dear Ross,
> ----
> From: Ross Altman <altman...@husky.neu.edu>
> Sent:  Tue, 14 Oct 2014 14:38:41 -0400
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Subject: Re: [sqlite] Make a database read-only?
> >
>
>> Thanks everyone for the helpful answers. Here's some context:
>>
>> The database I have is for academic purposes. Research groups will need to
>> be able to download it in order to do large-scale scans using it as input,
>> so putting it in a wrapper (in PHP, say) isn't useful. But, I don't want
>> someone else to take it, add to it, and put it online somewhere else, so
>> that there are multiple versions floating around the web. I don't mind if
>> there are multiple COPIES, but I want to make sure that they're all the
>> same.
>>
>>
> To a degree this sounds like the Streisand effect:
> https://en.wikipedia.org/wiki/Streisand_effect
>
>
> Pasting the sha256 is your best bet, but anyone who's going to verify the
> sha256 can already dump the database as explained by Clemens Ladisch.
>
>  Thanks again,
>> Ross
>>
>>
>
> --
> inum: 883510009027723
> sip: jungleboo...@sip2sip.info
> xmpp: jungle-boo...@jit.si
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Ross Altman]

Thanks everyone for the helpful answers. Here's some context:

The database I have is for academic purposes. Research groups will need to
be able to download it in order to do large-scale scans using it as input,
so putting it in a wrapper (in PHP, say) isn't useful. But, I don't want
someone else to take it, add to it, and put it online somewhere else, so
that there are multiple versions floating around the web. I don't mind if
there are multiple COPIES, but I want to make sure that they're all the
same.

Thanks again,
Ross

On Tue, Oct 14, 2014 at 12:16 PM, Igor Tandetnik  wrote:

> On 10/14/2014 10:12 AM, John Hascall wrote:
>
>> Some code you may find useful to enforce the readonly byte
>>
>
> Of course, anyone smart enough to change the byte from read-only to
> read-write before making changes, would also be smart enough to set it back
> afterwards.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Make a database read-only?

[Ross Altman]

Hi Martin,

Thank you, I'll definitely look into that. It's unfortunate that there
isn't a simpler way to do this... oh well.

Best,
Ross

On Tue, Oct 14, 2014 at 7:22 AM, Martin Engelschalk <
engelsch...@codeswift.com> wrote:

> Hello Ross,
>
> you could add triggers to all tables that RAISE(ROLLBACK, 'Forbidden') on
> all operations (insert, update and delete), see
> http://www.sqlite.org/lang_createtrigger.html, bottom of the page.
> However, it is difficult to see how to stop the downloaders from removing
> these triggers or indeed any other mechanism to prevent changes to the
> data. It is their file after download
>
> HTH
> Martin
>
> Am 14.10.2014 08:19, schrieb Ross Altman:
>
>> I need to host a fixed, unchanging database online, and I want to make
>> sure
>> that anyone who downloads it cannot add to it. Is there any way to set the
>> permissions to be read-only within sqlite?
>>
>> Thanks,
>> Ross
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> --
>
> *Codeswift GmbH *
> Kräutlerweg 20a
> A-5020 Salzburg
> Tel: +49 (0) 8662 / 494330
> Mob: +49 (0) 171 / 4487687
> Fax: +49 (0) 3212 / 1001404
> engelsch...@codeswift.com
> www.codeswift.com / www.swiftcash.at
>
> Codeswift Professional IT Services GmbH
> Firmenbuch-Nr. FN 202820s
> UID-Nr. ATU 50576309
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Make a database read-only?

[Ross Altman]

I need to host a fixed, unchanging database online, and I want to make sure
that anyone who downloads it cannot add to it. Is there any way to set the
permissions to be read-only within sqlite?

Thanks,
Ross
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users