Re: A suggested roadmap

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

- --Henri Bergius wrote on 2006-02-09 17:05:
>> Again for the Record: Give me (in 1.7) a QB that can query  
>> parameters flexibly
>> enough, and I should be able to speed this up.
> 
> Could this work as a joined query?

Only partly. Thing is that we have a special problem here: the existance of a
parameter is purely optional and one parameter row equals one parameter *field*.

For example: We have that parameter 'midcom/hide', which is set to true if the
object should be hidden. If it is unset (or false), the object is shown.

In SQL this is a moderatly complex problem, should be something like this (sorry
for the limited formatting):

...
select ...
  from table as t left join record_extension as r
    on (t.id = r.oid and r.tablename="table")
  where r.name="hide" and r.value="1";

This will leave one potential problem: If the parameter is undefined, all
joined' fields are NULL. According to DB science, you need to exclude NULL
explicitly, as all comparision with null are undefined:

...
select ...
  from table as t left join record_extension as r
    on (t.id = r.oid and r.tablename="table" and r.name="hide")
  where r.value NOT NULL and r.value="1";


So far so good. It gets really eerie if you have more then one parameter you
want to query. Using the same strategy, you'd have one join for each parameter
you want to query. Even with the ON ... limiters.

The only way I'd see around this would be a subquery:

select ... from table as t ...
  where id NOT IN
  (
    select distinct(oid) from record_extension where
          r.tablename="table"
      and r.oid = t.id
      and r.name = "hide"
      and r.value = "1"
  );

That subquery would have to be duplicated for every other parameter to be queried.

Note the negating query, you cannot ask for r.value="0" as not every record in t
has a record in record_extension.

While this would have a certain elegance, it'll require extending the QB API on
the PHP side (neccessary anyway) and, above all, a MySQL version supporting
subqueries. Either 4.0 or 4.1 does. Again, I personally would have no problem
making that version mandatory. Upgrading to these versions has proven seamlessly
to me (with the exception of DB charset conversions, where MySQL can't be blamed
for).

Using classic, precomputed lists (where the IN clause contents are precomputed
in a separate query) are *absolutely* no option for me. In short: that practice
is usually error prone and much less efficient as a real subquery.


Live long and Prosper!
Torben Nehmer

- --
Torben Nehmer, Guenzburg, Bavaria, Germany
http://www.nathan-syntronics.de, mailto:[email protected]
PGP Public Key: https://www.link-m.de/pgp/t.nehmer.asc
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFD62zLJPh4Kn6d5FYRAijZAKCP1Jmnm0cGVLl10QDXQTwaeFH0twCfX+vt
njTpcHylbHSKgPRkczd/9sc=
=qXoj
-----END PGP SIGNATURE-----