On 23 Jan 2015, at 4:15pm, Emmanouil Karvounis <man...@di.uoa.gr> wrote:
> In short, we have two tables that are already sorted on a combination of > two fields There is no such thing as a 'sorted table' in SQL. Each table is a set of rows and the rows have no order. If you want to make it easy for SQL to access a table's rows in a particular order, create an index or make that order the table's primary key (which is another way of making an index). > select c1, c2, myagg(*) from ( > select * from tableA > union all > select * from tableB > ) > group by c1, c2; This command tells SQL that you want to construct a list of every row of tableA and every row of tableB. In other words, if you have 300 rows in tableA and 500 rows in tableB, you are telling SQL to construct a new table of 800 rows. And because this table doesn't yet exist, it doesn't have any indexes so it can't be searched quickly. Is that what you wanted ? Is there a good reason for needing this data in two separate tables rather than one for which you can create an index on (c1, c2) ? Do the groups occur entirely within one table or do you have to add the tables together before SQL can figure out the groups. > where tableA, tableB have primary key (c1, c2) and their schema comprises 3 > integers: c1, c2, and prop. It might be worth testing with something like 'total(*)' just to make sure it isn't your own function which is causing the problems. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
