[dev] SQL shares: to join or not to join...

[Didi Rieder]

Quoting duck <duck at obala.net>:

> On Mon, 2008-05-19 at 17:58 +0200, Didi Rieder wrote:
>> Hi,
>>
>> I noticed that the original query for querying shares takes twice as
>> long as my modified query on a mySQL 5.0 database.
>>
>> Original:
>> mysql> SELECT SQL_NO_CACHE s.*  FROM nag_shares s  LEFT JOIN
>> nag_shares_users AS u ON u.share_id = s.share_id WHERE s.share_owner =
>> 'riederd at mbox' OR s.perm_creator & 4 OR s.perm_default & 4 OR (
>> u.user_uid = 'riederd at mbox' AND u.perm & 4) ORDER BY s.attribute_name
>> ASC;
>> +----------+--------------+--------------+-------------+--------------+--------------+------------+----------------------------+----------------+
>> | share_id | share_name   | share_owner  | share_flags | perm_creator
>> | perm_default | perm_guest | attribute_name             |
>> attribute_desc |
>> +----------+--------------+--------------+-------------+--------------+--------------+------------+----------------------------+----------------+
>> |    19358 | riederd at mbox | riederd at mbox |           1 |            0
>> |            0 |          0 | Rieder Dietmar's Task List | NULL
>>     |
>> +----------+--------------+--------------+-------------+--------------+--------------+------------+----------------------------+----------------+
>> 1 row in set (0.09 sec)
>>
>> Modified:
>> mysql> SELECT SQL_NO_CACHE s.*  FROM nag_shares s, nag_shares_users u
>> WHERE u.share_id = s.share_id AND (s.share_owner = 'riederd at mbox' OR
>> s.perm_creator & 4 OR s.perm_default & 4 OR ( u.user_uid =
>> 'riederd at mbox' AND u.perm & 4)) ORDER BY s.attribute_name ASC;
>> +----------+--------------+--------------+-------------+--------------+--------------+------------+----------------------------+----------------+
>> | share_id | share_name   | share_owner  | share_flags | perm_creator
>> | perm_default | perm_guest | attribute_name             |
>> attribute_desc |
>> +----------+--------------+--------------+-------------+--------------+--------------+------------+----------------------------+----------------+
>> |    19358 | riederd at mbox | riederd at mbox |           1 |            0
>> |            0 |          0 | Rieder Dietmar's Task List | NULL
>>     |
>> +----------+--------------+--------------+-------------+--------------+--------------+------------+----------------------------+----------------+
>> 1 row in set (0.04 sec)
>>
>> (Please note that I put the SQL_NO_CACHE to avoid caching which allows
>> for a better comparison, it is not intended for production)
>>
>> I think when hundreds of users are simultaneously using the system
>> this could improve the response times.
>> Does somebody else think that it would make sense to change this.
>>
>> Didi
>
> I guess this wont work. In the modified query you assume that there are
> some records for a share in both tables. But of course this is not
> always true.

Hmm... I thought that at least the default share is always in both tables.
But maybe I didn't get everything right.

Didi

-- 
-------------------------
Didi Rieder
adrieder at sbox.tugraz.at
PGPKey ID: 3431D0B0
-------------------------