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

[Didi Rieder]

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

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