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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
