Hi Tom!

Tom Conlon wrote:

> Example Usage (Old):
> --------------------
> SELECT c.contactid, c.surname, c.forename, c.dob
> FROM Contact c
> WHERE (c.typeid in (4,5,6,7,12,13,14,15)) AND
> (c.areaid IN (1475, 225,222)
>
> PLAN:
> (C INDEX
> (IDX_CONTACT_AREAID,
> IDX_CONTACT_AREAID,
> IDX_CONTACT_AREAID,
> IDX_CONTACT_TYPEID,
> IDX_CONTACT_TYPEID,
> IDX_CONTACT_TYPEID,
> IDX_CONTACT_TYPEID,
> IDX_CONTACT_TYPEID,
> IDX_CONTACT_TYPEID,
> IDX_CONTACT_TYPEID,
> IDX_CONTACT_TYPEID))

I think this seems like a good select statement and plan, it could be
marginally improved to

SELECT c.contactid, c.surname, c.forename, c.dob
FROM Contact c
WHERE (c.typeid between 4 and 7 or c.typeid between 12 and 15) AND
(c.areaid IN (1475, 225,222)

but unless you have more complex cases where the 'in list' gets much
bigger (there is a limit as to how many items there can be, at least in
Firebird 1.5), I don't think the speed benefit of doing such a change
would be worth doing.

> Example Usage (New):
> --------------------
>
> CREATE TABLE QRYCRITERIA
> (
> CRITERIACOLUMN VARCHAR(50) NOT NULL,
> CRITERIAVALUE VARCHAR(255) NOT NULL,
> USERID INTEGER NOT NULL
> );
>
> CREATE INDEX IDXQRYCRITERIA_CRITERIACOLUMN ON QRYCRITERIA
> (CRITERIACOLUMN, USERID);
> CREATE INDEX IDXQRYCRITERIA_USERID ON QRYCRITERIA (USERID);

Here you've got a commit, right?

> DELETE FROM QRYCRITERIA;
> INSERT INTO QRYCRITERIA VALUES ('TYPEID', 4 );
> ditto for 5,6,7,12,13,14,15
> INSERT INTO QRYCRITERIA VALUES ('AREAID', 1475 );
> ditto for 225,222
>
> SELECT c.contactid, c.surname, c.forename, c.dob
> FROM Contact c
> JOIN QryCriteria q1 on (q1.userid=1) and (q1.CRITERIACOLUMN='TYPEID')
> JOIN QryCriteria q2 on (q2.userid=1) and (q2.CRITERIACOLUMN='AREAID')
> WHERE (c.typeid in (q1.criteriavalue)) AND
> (c.areaid in (q2.criteriavalue))
>
> PLAN:
> JOIN
> (Q1 INDEX (IDXQRYCRITERIA_CRITERIACOLUMN),
> Q2 INDEX (IDXQRYCRITERIA_CRITERIACOLUMN),
> C INDEX (IDX_CONTACT_AREAID,IDX_CONTACT_TYPEID))

How many different values are there in CRITERIACOLUMN? If there are only
a handful, NATURAL could be a better option than using an index.

I think I'd do things the other way around, if for no other reason, then
because it seems more logical to me:

SELECT c.contactid, c.surname, c.forename, c.dob
FROM Contact c
JOIN QryCriteria q1 on c.typeid = q1.criteriavalue
JOIN QryCriteria q2 on c.typeid = q2.criteriavalue
WHERE q1.userid=1
and q1.CRITERIACOLUMN='TYPEID'
and q2.userid=1
and q2.CRITERIACOLUMN='AREAID'

Whichever you choose, I'd be surprised if the new usage would be any
better than the old usage from a performance point of view (I'd expect
the new to be slightly slower). Hence, provided the IN list doesn't get
all too big, the question to ask yourself is rather: What's most simple
to understand and maintain? I don't know the answer to that question...

HTH,
Set