Solved the problem.
For reference, field instanceid in the instance_fields needs to be
declared as an integer, thus:
CREATE TABLE instance_fields (
instanceid integer references instances(instanceid),
field,
subscript default NULL,
value,
primary key (instanceid, field, subscript) );
DRH,
Is this expected behaviour? I'd have thought the manifest typing would
have seen to that, but it appears that join fields need to be the same
type.
Christian
Christian Smith uttered:
Hello JOIN expoerts:)
I have the following schema:
CREATE TABLE instances (
instanceid integer primary key,
type,
instance);
CREATE INDEX instances_type_instance ON instances(type,instance);
CREATE TABLE instance_fields (
instanceid references instances(instanceid),
field,
subscript default NULL,
value,
primary key (instanceid, field, subscript) );
The idea is that instances catalogs instances of an arbitrary type, and
instance_fields records the data of each instance as name value pairs.
instance_fields.subscript is for arrays as field values (unused ATM).
Now, suppose I have an instance type of 'event', which contains the following
fields:
count
first
last
severity
summary
node
source
Then the following view will select all the 'event' instances, formtted into
a nice table view:
create view events_instance as
select
i.instance as event,
count.value AS count,
first.value AS first,
last.value AS last,
severity.value AS severity,
summary.value AS summary,
node.value AS node,
source.value AS source
FROM instances as i
inner join instance_fields as count using(instanceid)
inner join instance_fields as first using (instanceid)
inner join instance_fields as last using (instanceid)
inner join instance_fields as severity using (instanceid)
inner join instance_fields as summary using (instanceid)
inner join instance_fields as node using (instanceid)
inner join instance_fields as source using (instanceid)
WHERE
i.type = 'event' AND
first.field = 'first' AND
count.field = 'count' AND
last.field = 'last' AND
severity.field = 'severity' AND
summary.field = 'summary' AND
node.field = 'node' AND
source.field = 'source';
The problem is that the first join is not being done using the primary key
index (on count in this case). The query plan is below:
0|0|TABLE instances AS i WITH INDEX instances_type_instance
1|1|TABLE instance_fields AS count
2|2|TABLE instance_fields AS first WITH INDEX
sqlite_autoindex_instance_fields_1
3|3|TABLE instance_fields AS last WITH INDEX
sqlite_autoindex_instance_fields_1
4|4|TABLE instance_fields AS severity WITH INDEX
sqlite_autoindex_instance_fields_1
5|5|TABLE instance_fields AS summary WITH INDEX
sqlite_autoindex_instance_fields_1
6|6|TABLE instance_fields AS node WITH INDEX
sqlite_autoindex_instance_fields_1
7|7|TABLE instance_fields AS source WITH INDEX
sqlite_autoindex_instance_fields_1
I'd expect count to be opened using sqlite_autoindex_instance_fields_1, which
refers to the (instanceid, field, subscript) primary key index, as the
instanceid is available from 'instances AS i'
Changing the order of the joins doesn't help. It's always the first join
which goes wrong. I've tried it with and without data in the tables, before
and after 'VACUUM ANALYZE'.
Anyone know what's wrong?
Cheers,
Christian
PS. This is using less than week old CVS HEAD version.
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
