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. <snip> Regards Kermit Mei _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
