On Mon, Dec 12, 2011 at 07:08, David Roe <r...@math.harvard.edu> wrote:
> On Mon, Dec 12, 2011 at 07:33, R. Andrew Ohana <andrew.oh...@gmail.com>
> wrote:
> > On Mon, Dec 12, 2011 at 06:12, David Roe <r...@math.harvard.edu> wrote:
> >>
> >> Yes. I labelled all of the columns as index=True.
> >
> >
> > You should not be indexing all columns, only the ones you are making many
> > queries upon.
>
> By "making queries upon" do you mean appear in the WHERE part of a
> SELECT statement? All of the columns appear somewhere: some in JOINs,
> some in testing for equality with a fixed base or exponent, and some
> in the output of the select statement. For reference, my queries look
> like
>
> SELECT factors.value, factors.prime
> FROM factors, bks
> WHERE factors.factor_id=bks.factor_id AND bks.b='2' AND (bks.s='1' AND
> (bks.k='140' OR bks.k='70' OR bks.k='35') OR bks.s='-1' AND
> (bks.k='35' OR bks.k='7' OR bks.k='5'))guide
>
> Let me know if there's a more efficient way to do this.
>
I would only put indices on the factor_id, bks.b, blk.s and maybe the bks.k
columns (you should test to see if this has a noticeable difference in
performance). Not sure if it is faster, but a cleaner way to write the
above query is
SELECT value,prime FROM factors,bks
USING(factor_id)
WHERE bks.b='2' AND (bks.s=1 AND bks.k IN ('140', '70', '35') OR bks.s='-1'
AND bks.k IN ('35', '7', '5'))
You should also look at how to use the second argument in the python api,
it will make python ints/longs/floats/strings into the corresponding sql
types, and is the suggested manner in which to interact with databases.
>> I don't know if that's sufficient, or if the size-on-disk would be a lot
>> less without redundant indices: I haven't used SQL in Sage much.
>
>
> By this, do you mean the SQLDatabase class defined in
sage.databases.sql_db
> (or sage.database.database for <sage-4.7.2)? If so, this is not necessary,
> all it does is provide a few extra functions to the python api for sqlite
> (such as pretty printing of queries). Assuming you are not creating a new
> class, then you might as well use the python api directly. If you are
> creating class, and want the extra functionality from SQLDatabase, you can
> still use the python api directly with self.__database.
My plan was to inherit from SQLDatabase and add some functionality. I
> assume you mean __connection__ to access the underlying sqlite
> database? I'll probably code the SQL querries by hand since there's
> only a few I'll actually be using.
>
Yes, I did mean __connection__ (been a little while since I've used the
object), and that plan is exactly what I did with the cremona database.
--
Andrew
--
To post to this group, send an email to sage-devel@googlegroups.com
To unsubscribe from this group, send an email to
sage-devel+unsubscr...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/sage-devel
URL: http://www.sagemath.org
