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?? 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. > 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. HTH, John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
