sqlite: merging two tables on a common key

A simple sqlite query is running impossibly slowly an开发者_Python百科d I'm not sure why. I have two tables A and B, they have the same primary keys and different columns. I want to create table C that has the primary key plus all of the columns in A and all of the columns in B. I'm running this via a python connection cursor cs.

   cs.execute('create table tableC as select a.*, b.*
   from tableA a left join tableB b
   on a.rid = b.id')

left join is ok since the two tables have equal number of rows. instead of writing out all of the column names in one of the tables, I allowed the id column to be duplicated.

each table has 50,000 rows and about 200 columns.

is there a better way to do this?

You could create a view that will be a live image of the two tables but looks like a new table. It won't be any faster than the select without the view, though.

If you need one-shot conversion (or update when needed, maybe even by trigger), you could see this answer.