[how to store userpermissions in a tree in mysql] Hi,
after a long time of try and error I have this as a result: mysql> select * from node; +---------+---------+---------+-----+-----+------+------+------+ | node_id | root_id | payload | lft | rgt | a | c | d | +---------+---------+---------+-----+-----+------+------+------+ | 1 | 0 | master | 1 | 8 | 1 | 1 | 1 | | 2 | 0 | user1 | 2 | 5 | 0 | 1 | 1 | | 3 | 0 | user2 | 6 | 7 | 1 | 1 | 1 | | 4 | 0 | user3 | 3 | 4 | 1 | 1 | 1 | +---------+---------+---------+-----+-----+------+------+------+ mysql> SELECT node1.payload,COUNT(*) AS level FROM node AS node1, node as node2 where node1.lft between node2.lft and node2.rgt group by node1.lft; +---------+-------+ | payload | level | +---------+-------+ | master | 1 | | user1 | 2 | | user3 | 3 | | user2 | 2 | +---------+-------+ mysql> select min(node2.a),min(node2.c),min(node2.d) from node as node1, node as node2 where (node1.lft between node2.lft and node2.rgt) and node1.payload = "user3"; +--------------+--------------+--------------+ | min(node2.a) | min(node2.c) | min(node2.d) | +--------------+--------------+--------------+ | 0 | 1 | 1 | +--------------+--------------+--------------+ mysql> select min(node2.a),min(node2.c),min(node2.d) from node as node1, node as node2 where (node1.lft between node2.lft and node2.rgt) and node1.payload = "user1"; +--------------+--------------+--------------+ | min(node2.a) | min(node2.c) | min(node2.d) | +--------------+--------------+--------------+ | 0 | 1 | 1 | +--------------+--------------+--------------+ mysql> select min(node2.a),min(node2.c),min(node2.d) from node as node1, node as node2 where (node1.lft between node2.lft and node2.rgt) and node1.payload = "user2"; +--------------+--------------+--------------+ | min(node2.a) | min(node2.c) | min(node2.d) | +--------------+--------------+--------------+ | 1 | 1 | 1 | +--------------+--------------+--------------+ Can someone have a look at it? Is it right? If it is right - how much cpu will this type of query cost if I have 10000 userids? Thanks, Alexander Newald -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
