== Nested Set integrity check ==
Does a quick checksum check on a nested set tree. Adds all left and right values to check they sum up to the correct values and does some other little checks.
mysql> SELECT
#
# The sum of all lft and rgt values should add up (does not guarantee
# correct structure, but is good at catching borked data.
#
# In a correct nested set, this equation is true:
# (amount_of_elements+1)*(amount_of_elements/2)= sum_of_all_lft_and_rgt
#
# This is because 1+2+3+4+5+6+7+8 = 8+1 + 2+7 + 3+6 + 4+5 = (8+1) * (8/2)
#
IF((SUM(lft)+SUM(rgt)/1) = ((MAX(rgt)+1) * (MAX(rgt)/2)), 'valid', 'invalid')
AS lft_rgt_checksum,
#
# Each lft value must not appear more than once
#
IF((SELECT COUNT(*) AS cnt FROM genres GROUP BY lft ORDER BY cnt DESC LIMIT 1)=1, 'valid', 'invalid')
AS lft_count,
#
# Each rgt value must not appear more than once
#
IF((SELECT COUNT(*) AS cnt FROM genres GROUP BY rgt ORDER BY cnt DESC LIMIT 1)=1, 'valid', 'invalid')
AS rgt_count,
#
# Smallest lft value should be 1
#
IF((SELECT MIN(lft))=1, 'valid', 'invalid')
AS min_left,
#
# Largest rgt value should be twice the number of elements
#
IF((SELECT MAX(rgt)/2 FROM genres)=(SELECT COUNT(*)/1 FROM genres), 'valid', 'invalid')
AS max_rgt
FROM genres;
+------------------+-----------+-----------+---------+----------+
| lft_rgt_checksum | lft_count | rgt_count | max_rgt | min_left |
+------------------+-----------+-----------+---------+----------+
| valid | valid | valid | valid | valid |
+------------------+-----------+-----------+---------+----------+
1 row in set (0.00 sec)
mysql>