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