Re: [sqlite] How to speed up a query between two tables?
On Tue, 2009-09-15 at 22:31 +1000, John Machin wrote: > On 15/09/2009 7:25 PM, Kermit Mei wrote: > > On Tue, 2009-09-15 at 17:37 +1000, John Machin wrote: > >> On 15/09/2009 4:47 PM, Kermit Mei wrote: > >>> > >>> sqlite> SELECT HomeDev.text, ZPhDev.id > >>>...> FROM ZPhDev > >>>...> INNER JOIN HomeDev ON (HomeDev.id = ZPhDev.id) > >> Are you sure that you mean ZPhDev.id in the ON clause?? > > > > Oh,I'm sorry, that should be : > > CREATE VIEW ZHView AS SELECT HomeDev.text, ZPhDev.id FROM ZPhDev INNER > > JOIN HomeDev ON (HomeDev.id = ZPhDev.HomeDevId) ORDER By HomeDev.text; > > > > > >> If so, what's > >> the point of having the ZPhDev.HomeDevId which isn't used and whose name > >> suggests that it should be used? > > > You didn't answer this question: > > >> If not, what's the point of having > >> ZphDev.id? Could the second table be collapsed into an isaZPhDev > >> (true/false) column in the first table? ZphDev.id is used in other tables to reference the items in ZphDev, HomeDev table hasn't used it directly. > > >>> Then, How can I speed up this kind of operation? > >> Standard advice: ensure that you have indexes on whatever the join > >> columns should be. You might get more help if you showed the CREATE > >> TABLE (including pk and any other constraints) and CREATE INDEX > >> statements, and added a note about the relationship between the two tables. > > And the relationship between the tables is ..?? In HomeDev, some items is a description for every items in ZPhDev. > > The homeDev table: > > CREATE TABLE HomeDev (color TEXT, secId NUMERIC, background TEXT, > > pageNum int, icon_y int, icon_x int, size_y int, size_x int, pos_y int, > > pos_x int, id INTEGER PRIMARY KEY, text varchar(15), icon varchar(50), > > type int); > > > > The ZPhDev table: > > CREATE TABLE ZPhDev (HomeDevId NUMERIC, id INTEGER PRIMARY KEY); > > No relationship comments, no foreign key clause, ... no useful info. > > > How can I create index for them? > > With a CREATE INDEX statement. Please consider reading an SQL text ... > anyone have any suggestions for Kermit? I'm sorry for my poor English. I don't ask you about SQL syntax, I just want to know create index for which columns. > > HomeDev.id already has an index, automatically created because it is a > primary key and in this case it's not an extra index, it's the rowid index. > > >>> Use view like this: > >>> > >>> sqlite> CREATE VIEW ZHview AS > >>>...> SELECT HomeDev.text, ZPhDev.id > >>>...> FROM ZPhDev > >>>...> INNER JOIN HomeDev ON (HomeDev.id = ZPhDev.id) > >>>...> ORDER By HomeDev.text; > >>> > >>> And can it speed up my operation? > >> Read this: http://en.wikipedia.org/wiki/View_%28database%29 and see if > >> you can find any indication that a (non-materialised) view speeds > >> anything up at runtime. > > > > Thanks,but I can't find anything about how can I speed it up on wiki. > > Three possibilities, any one or more or all of which could be true: the > information about how a non-materialised view can speed up a query is: > (1) there but you can't find it. > (2) not there because the wiki authors left it out for some reason. > (3) not there because such a view can't speed up a query. > > Here's a hint: re-read the section on Equivalence, which says in essence > that selecting from a view makes the programmer's job easier, but at > runtime it's just a text substitution exercise. There's a very close > parallel to using functions in procedural programming languages -- > replacing say 3 copies of the same code with one copy plus 3 calls to it > has little effect on the runtime speed. Yes, I see. Thank you,very much. For some reason, I may waste your time, I'm sorry for that. Kermit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up a query between two tables?
On 15/09/2009 7:25 PM, Kermit Mei wrote: > On Tue, 2009-09-15 at 17:37 +1000, John Machin wrote: >> On 15/09/2009 4:47 PM, Kermit Mei wrote: >>> >>> sqlite> SELECT HomeDev.text, ZPhDev.id >>>...> FROM ZPhDev >>>...> INNER JOIN HomeDev ON (HomeDev.id = ZPhDev.id) >> Are you sure that you mean ZPhDev.id in the ON clause?? > > Oh,I'm sorry, that should be : > CREATE VIEW ZHView AS SELECT HomeDev.text, ZPhDev.id FROM ZPhDev INNER > JOIN HomeDev ON (HomeDev.id = ZPhDev.HomeDevId) ORDER By HomeDev.text; > > >> If so, what's >> the point of having the ZPhDev.HomeDevId which isn't used and whose name >> suggests that it should be used? You didn't answer this question: >> If not, what's the point of having >> ZphDev.id? Could the second table be collapsed into an isaZPhDev >> (true/false) column in the first table? >>> Then, How can I speed up this kind of operation? >> Standard advice: ensure that you have indexes on whatever the join >> columns should be. You might get more help if you showed the CREATE >> TABLE (including pk and any other constraints) and CREATE INDEX >> statements, and added a note about the relationship between the two tables. And the relationship between the tables is ..?? > The homeDev table: > CREATE TABLE HomeDev (color TEXT, secId NUMERIC, background TEXT, > pageNum int, icon_y int, icon_x int, size_y int, size_x int, pos_y int, > pos_x int, id INTEGER PRIMARY KEY, text varchar(15), icon varchar(50), > type int); > > The ZPhDev table: > CREATE TABLE ZPhDev (HomeDevId NUMERIC, id INTEGER PRIMARY KEY); No relationship comments, no foreign key clause, ... no useful info. > How can I create index for them? With a CREATE INDEX statement. Please consider reading an SQL text ... anyone have any suggestions for Kermit? HomeDev.id already has an index, automatically created because it is a primary key and in this case it's not an extra index, it's the rowid index. >>> Use view like this: >>> >>> sqlite> CREATE VIEW ZHview AS >>>...> SELECT HomeDev.text, ZPhDev.id >>>...> FROM ZPhDev >>>...> INNER JOIN HomeDev ON (HomeDev.id = ZPhDev.id) >>>...> ORDER By HomeDev.text; >>> >>> And can it speed up my operation? >> Read this: http://en.wikipedia.org/wiki/View_%28database%29 and see if >> you can find any indication that a (non-materialised) view speeds >> anything up at runtime. > > Thanks,but I can't find anything about how can I speed it up on wiki. Three possibilities, any one or more or all of which could be true: the information about how a non-materialised view can speed up a query is: (1) there but you can't find it. (2) not there because the wiki authors left it out for some reason. (3) not there because such a view can't speed up a query. Here's a hint: re-read the section on Equivalence, which says in essence that selecting from a view makes the programmer's job easier, but at runtime it's just a text substitution exercise. There's a very close parallel to using functions in procedural programming languages -- replacing say 3 copies of the same code with one copy plus 3 calls to it has little effect on the runtime speed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to speed up a query between two tables?
On Tue, 2009-09-15 at 17:37 +1000, John Machin wrote: > On 15/09/2009 4:47 PM, Kermit Mei wrote: > > Hello community! > > > > I have two tables: > > > > HomeDev(id, text, icon) > > > > and > > > > ZPhDev(id,HomeDevId) > > > > Now, I must usually access(read-only) the items: > > > > sqlite> SELECT HomeDev.text, ZPhDev.id > >...> FROM ZPhDev > >...> INNER JOIN HomeDev ON (HomeDev.id = ZPhDev.id) > > Are you sure that you mean ZPhDev.id in the ON clause?? Oh,I'm sorry, that should be : CREATE VIEW ZHView AS SELECT HomeDev.text, ZPhDev.id FROM ZPhDev INNER JOIN HomeDev ON (HomeDev.id = ZPhDev.HomeDevId) ORDER By HomeDev.text; > If so, what's > the point of having the ZPhDev.HomeDevId which isn't used and whose name > suggests that it should be used? If not, what's the point of having > ZphDev.id? Could the second table be collapsed into an isaZPhDev > (true/false) column in the first table? > > >...> ORDER By HomeDev.text; > > > > Then, How can I speed up this kind of operation? > > Standard advice: ensure that you have indexes on whatever the join > columns should be. You might get more help if you showed the CREATE > TABLE (including pk and any other constraints) and CREATE INDEX > statements, and added a note about the relationship between the two tables. The homeDev table: CREATE TABLE HomeDev (color TEXT, secId NUMERIC, background TEXT, pageNum int, icon_y int, icon_x int, size_y int, size_x int, pos_y int, pos_x int, id INTEGER PRIMARY KEY, text varchar(15), icon varchar(50), type int); The ZPhDev table: CREATE TABLE ZPhDev (HomeDevId NUMERIC, id INTEGER PRIMARY KEY); How can I create index for them? > > Use view like this: > > > > sqlite> CREATE VIEW ZHview AS > >...> SELECT HomeDev.text, ZPhDev.id > >...> FROM ZPhDev > >...> INNER JOIN HomeDev ON (HomeDev.id = ZPhDev.id) > >...> ORDER By HomeDev.text; > > > > And can it speed up my operation? > > Read this: http://en.wikipedia.org/wiki/View_%28database%29 and see if > you can find any indication that a (non-materialised) view speeds > anything up at runtime. Thanks,but I can't find anything about how can I speed it up on wiki. Regards Kermit Mei ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
