Quantcast

Multiple tables or not

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Multiple tables or not

Joshua Grauman-2
Hello all,

I am wondering what would be faster for sqlite- Suppose I have a table
with ~400,000 rows and ~20 columns. Suppose I only care about read speed
of the table, and that each of the columns contains 8-bit or 32-bit
integers, and the first column is an ID for the row. Suppose I also have
an index on the first column. If I only use the first 3 of the columns
regularly and only really want to optimize the speed of reading those
first 3 columns, would it make sense to split the table into two tables,
where the first column (the ID) is identical, but the first table only has
the first 3 columns and the second table has the rest of the columns.
Would that make reading the smaller table with only the 3 columns
noticably faster (due to less page faults/etc.)?

Josh
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multiple tables or not

Richard Hipp-3
On 3/4/17, Joshua Grauman <[hidden email]> wrote:
> Hello all,
>
> I am wondering what would be faster for sqlite- Suppose I have a table
> with ~400,000 rows and ~20 columns. Suppose I only care about read speed
> of the table, and that each of the columns contains 8-bit or 32-bit
> integers, and the first column is an ID for the row. Suppose I also have
> an index on the first column.

If the ID column is an INTEGER PRIMARY KEY, then the index will be
pointless, will take up space and slow down INSERT and UPDATE
operations, but will never actually be used for queries.

> If I only use the first 3 of the columns
> regularly and only really want to optimize the speed of reading those
> first 3 columns, would it make sense to split the table into two tables,
> where the first column (the ID) is identical, but the first table only has
> the first 3 columns and the second table has the rest of the columns.
> Would that make reading the smaller table with only the 3 columns
> noticably faster (due to less page faults/etc.)?
>

Yes, possibly so.  I don't know by how much, but it does make since
that queries would be faster this way.


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multiple tables or not

Joshua Grauman-2
Thanks! If I am able to run real world tests to see if it is perceptibly
faster, I'll report back!

Josh

> On 3/4/17, Joshua Grauman <[hidden email]> wrote:
>> Hello all,
>>
>> I am wondering what would be faster for sqlite- Suppose I have a table
>> with ~400,000 rows and ~20 columns. Suppose I only care about read speed
>> of the table, and that each of the columns contains 8-bit or 32-bit
>> integers, and the first column is an ID for the row. Suppose I also have
>> an index on the first column.
>
> If the ID column is an INTEGER PRIMARY KEY, then the index will be
> pointless, will take up space and slow down INSERT and UPDATE
> operations, but will never actually be used for queries.
>
>> If I only use the first 3 of the columns
>> regularly and only really want to optimize the speed of reading those
>> first 3 columns, would it make sense to split the table into two tables,
>> where the first column (the ID) is identical, but the first table only has
>> the first 3 columns and the second table has the rest of the columns.
>> Would that make reading the smaller table with only the 3 columns
>> noticably faster (due to less page faults/etc.)?
>>
>
> Yes, possibly so.  I don't know by how much, but it does make since
> that queries would be faster this way.
>
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Multiple tables or not

Clemens Ladisch
In reply to this post by Joshua Grauman-2
Joshua Grauman wrote:
> would it make sense to split the table into two tables, where the
> first column (the ID) is identical, but the first table only has the
> first 3 columns and the second table has the rest of the columns.
> Would that make reading the smaller table with only the 3 columns
> noticably faster (due to less page faults/etc.)?

Yes, but you can get the same effect by creating a covering index, i.e.,
an index that contains these three columns:
<http://www.sqlite.org/queryplanner.html#covidx>

This index can be used for queries that scan the entire table, or that
do searches on the leftmost column(s) of the index.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...