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
