On Oct 15, 2010, at 17:32, Scott Hess wrote:
> Having a table with an owner_id, key, and value, with a unique index
> on (owner_id, key) will probably be more efficient than having a
> separate table per owner. Also, it will be easier to code safely,
> because bind parameters don't work on table names (I'm assuming you're
> using dynamic table names in this case - if so, watch out for johny
> drop tables).
...but there will also be a unique index on rowid, which will get large
and need to be maintained. I'm concerned that this alone could be limiting me
somewhat.
I have a similar application with a single table that I'd like to split
into more based on an identifier that appears in the table. All of my
operations are limited to one of these identifiers (though it's not indexed,
the lookup is always by rowid). Occasionally, I want to delete all records
based on an ID.
Bobby Tables is not relevant to my application as I know how to do my
bindings properly and have no confusion with data types (this is an integer) or
user data vs. executable code.
As a single table, I can easily have many tens of millions of rows.
Splitting it into 1,024 tables by a specific ID, I'd expect the each index to
be smaller and (at the very least), I'll have a far easier time deleting a
large chunk all at once.
I do intend to do some experimentation here, though it'd be helpful to
have some more detailed pointers as to why the intuition is wrong here.
--
Dustin Sallings
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
