These examples cover most of the queries being run in menu.inc, menu.module, book.module, etc. ===================================================== with the old keys and 2000 node links in 8 books: PRIMARY KEY (`mlid`), KEY `expanded_children` (`expanded`,`has_children`), KEY `router_path` (`router_path`), KEY `parents` (`p1`,`p2`,`p3`,`p4`,`p5`,`p6`,`p7`,`p8`,`p9`), KEY `menu_name_path` (`menu_name`,`link_path`), KEY `plid` (`plid`) mysql> EXPLAIN SELECT * FROM menu_links ml LEFT JOIN menu_router m ON ml.router_path=m.path WHERE menu_name = 'navigation' AND plid IN(0,3,16) ORDER BY p1, p2, p3, p4, p5, p6, p7, p8, p9; +----+-------------+-------+--------+---------------------+---------+---------+---------------------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+---------+---------+---------------------+------+-----------------------------+ | 1 | SIMPLE | ml | range | menu_name_path,plid | plid | 4 | NULL | 35 | Using where; Using filesort | | 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 767 | dp60.ml.router_path | 1 | | +----+-------------+-------+--------+---------------------+---------+---------+---------------------+------+-----------------------------+ 2 rows in set (0.01 sec) mysql> EXPLAIN SELECT * FROM menu_links ml LEFT JOIN menu_router m ON ml.router_path=m.path WHERE menu_name = 'navigation' AND plid IN(0) ORDER BY p1, p2, p3, p4, p5, p6, p7, p8, p9; +----+-------------+-------+--------+---------------------+---------+---------+---------------------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+---------+---------+---------------------+------+-----------------------------+ | 1 | SIMPLE | ml | ref | menu_name_path,plid | plid | 4 | const | 34 | Using where; Using filesort | | 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 767 | dp60.ml.router_path | 1 | | +----+-------------+-------+--------+---------------------+---------+---------+---------------------+------+-----------------------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT * FROM menu_links ml LEFT JOIN menu_router m ON ml.router_path=m.path WHERE menu_name = 'navigation' ORDER BY p1, p2, p3, p4, p5, p6, p7, p8, p9; +----+-------------+-------+--------+----------------+----------------+---------+---------------------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+----------------+----------------+---------+---------------------+------+-----------------------------+ | 1 | SIMPLE | ml | ref | menu_name_path | menu_name_path | 194 | const | 360 | Using where; Using filesort | | 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 767 | dp60.ml.router_path | 1 | | +----+-------------+-------+--------+----------------+----------------+---------+---------------------+------+-----------------------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT p1, p2, p3, p4, p5, p6, p7, p8 FROM menu_links WHERE menu_name = 'navigation' AND link_path ='admin/build/content'; +----+-------------+------------+------+----------------+----------------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+----------------+----------------+---------+-------------+------+-------------+ | 1 | SIMPLE | menu_links | ref | menu_name_path | menu_name_path | 961 | const,const | 1 | Using where | +----+-------------+------------+------+----------------+----------------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT mlid FROM menu_links WHERE menu_name = 'navigation' AND expanded != 0 AND has_children != 0 AND plid IN (105, 0, 3) AND mlid NOT IN (3, 0); +----+-------------+------------+-------+-----------------------------------------------+-------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+-----------------------------------------------+-------------------+---------+------+------+-------------+ | 1 | SIMPLE | menu_links | range | PRIMARY,expanded_children,menu_name_path,plid | expanded_children | 2 | NULL | 3 | Using where | +----+-------------+------------+-------+-----------------------------------------------+-------------------+---------+------+------+-------------+ 1 row in set (0.01 sec) mysql> EXPLAIN SELECT * FROM menu_links ml INNER JOIN menu_router m ON m.path=ml.router_path WHERE ml.link_path LIKE 'admin/%' AND ml.hidden >= 0 AND ml.module = 'system' AND m.number_parts > 2; +----+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------------+ | 1 | SIMPLE | ml | ALL | router_path | NULL | NULL | NULL | 2123 | Using where | | 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 767 | dp60.ml.router_path | 1 | Using where | +----+-------------+-------+--------+---------------+---------+---------+---------------------+------+-------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT * FROM menu_links ml LEFT JOIN menu_router m ON ml.router_path=m.path WHERE menu_name = 'book-toc-5' AND plid IN(0,120,133,151,179,332,1630) ORDER BY p1, p2, p3, p4, p5, p6, p7, p8, p9; +----+-------------+-------+--------+---------------------+---------+---------+---------------------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+---------+---------+---------------------+------+-----------------------------+ | 1 | SIMPLE | ml | range | menu_name_path,plid | plid | 4 | NULL | 45 | Using where; Using filesort | | 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 767 | dp60.ml.router_path | 1 | | +----+-------------+-------+--------+---------------------+---------+---------+---------------------+------+-----------------------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT * FROM menu_links ml INNER JOIN menu_router m ON ml.router_path=m.path INNER JOIN book b on b.mlid=ml.mlid WHERE p1=120 AND p2=133 ORDER BY p1, p2, p3, p4, p5, p6, p7, p8, p9; +----+-------------+-------+--------+-----------------------------+---------+---------+---------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-----------------------------+---------+---------+---------------------+------+-------------+ | 1 | SIMPLE | ml | ref | PRIMARY,router_path,parents | parents | 8 | const,const | 128 | Using where | | 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 767 | dp60.ml.router_path | 1 | | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | dp60.ml.mlid | 1 | | +----+-------------+-------+--------+-----------------------------+---------+---------+---------------------+------+-------------+ 3 rows in set (0.00 sec) ============================================== with NEW keys and the same 2000 node links in 8 books: PRIMARY KEY (`mlid`), KEY `path_menu` (`link_path`(128),`menu_name`), KEY `menu_plid_expand_child` (`menu_name`,`plid`,`expanded`,`has_children`), KEY `menu_parents` (`menu_name`,`p1`,`p2`,`p3`,`p4`,`p5`,`p6`,`p7`,`p8`,`p9`), KEY `router_path` (`router_path`(128)) **NOTE compared to above this may be a little better - examines fewer rows: mysql> EXPLAIN SELECT * FROM menu_links ml LEFT JOIN menu_router m ON ml.router_path=m.path WHERE menu_name = 'navigation' AND plid IN(0,3,16) ORDER BY p1, p2, p3, p4, p5, p6, p7, p8, p9; +----+-------------+-------+--------+-------------------------------------+------------------------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-------------------------------------+------------------------+---------+------+------+-----------------------------+ | 1 | SIMPLE | ml | range | menu_parents,menu_plid_expand_child | menu_plid_expand_child | 70 | NULL | 25 | Using where; Using filesort | | 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 767 | func | 1 | | +----+-------------+-------+--------+-------------------------------------+------------------------+---------+------+------+-----------------------------+ 2 rows in set (0.00 sec) **NOTE compared to above this may be a little better - examines fewer rows: mysql> EXPLAIN SELECT * FROM menu_links ml LEFT JOIN menu_router m ON ml.router_path=m.path WHERE menu_name = 'navigation' AND plid IN(0) ORDER BY p1, p2, p3, p4, p5, p6, p7, p8, p9; +----+-------------+-------+--------+-------------------------------------+------------------------+---------+-------------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-------------------------------------+------------------------+---------+-------------+------+-----------------------------+ | 1 | SIMPLE | ml | ref | menu_parents,menu_plid_expand_child | menu_plid_expand_child | 70 | const,const | 23 | Using where; Using filesort | | 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 767 | func | 1 | | +----+-------------+-------+--------+-------------------------------------+------------------------+---------+-------------+------+-----------------------------+ 2 rows in set (0.00 sec) **NOTE compared to above this is much better - uses the index for the ORDER BY: mysql> EXPLAIN SELECT * FROM menu_links ml LEFT JOIN menu_router m ON ml.router_path=m.path WHERE menu_name = 'navigation' ORDER BY p1, p2, p3, p4, p5, p6, p7, p8, p9; +----+-------------+-------+--------+-------------------------------------+--------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-------------------------------------+--------------+---------+-------+------+-------------+ | 1 | SIMPLE | ml | ref | menu_parents,menu_plid_expand_child | menu_parents | 66 | const | 199 | Using where | | 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 767 | func | 1 | | +----+-------------+-------+--------+-------------------------------------+--------------+---------+-------+------+-------------+ 2 rows in set (0.01 sec) **NOTE same as above mysql> EXPLAIN SELECT p1, p2, p3, p4, p5, p6, p7, p8 FROM menu_links WHERE menu_name = 'navigation' AND link_path ='admin/build/content'; +----+-------------+------------+------+-----------------------------------------------+-----------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+-----------------------------------------------+-----------+---------+-------------+------+-------------+ | 1 | SIMPLE | menu_links | ref | menu_parents,path_menu,menu_plid_expand_child | path_menu | 196 | const,const | 1 | Using where | +----+-------------+------------+------+-----------------------------------------------+-----------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) **NOTE compared to above this is very similar: mysql> EXPLAIN SELECT mlid FROM menu_links WHERE menu_name = 'navigation' AND expanded != 0 AND has_children != 0 AND plid IN (105, 0, 3) AND mlid NOT IN (3, 0); +----+-------------+------------+-------+---------------------------------------------+------------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------------------------------------+------------------------+---------+------+------+-------------+ | 1 | SIMPLE | menu_links | range | PRIMARY,menu_parents,menu_plid_expand_child | menu_plid_expand_child | 72 | NULL | 5 | Using where | +----+-------------+------------+-------+---------------------------------------------+------------------------+---------+------+------+-------------+ 1 row in set (0.00 sec) **NOTE compared to above this is a much better - the query above does not use any index: mysql> EXPLAIN SELECT * FROM menu_links ml INNER JOIN menu_router m ON m.path=ml.router_path WHERE ml.link_path LIKE 'admin/%' AND ml.hidden >= 0 AND ml.module = 'system' AND m.number_parts > 2; +----+-------------+-------+--------+-----------------------+-----------+---------+---------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-----------------------+-----------+---------+---------------------+------+-------------+ | 1 | SIMPLE | ml | range | router_path,path_menu | path_menu | 386 | NULL | 156 | Using where | | 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 767 | dp60.ml.router_path | 1 | Using where | +----+-------------+-------+--------+-----------------------+-----------+---------+---------------------+------+-------------+ 2 rows in set (0.00 sec) **NOTE compared to above this may be a little better - examines fewer rows: mysql> EXPLAIN SELECT * FROM menu_links ml LEFT JOIN menu_router m ON ml.router_path=m.path WHERE menu_name = 'book-toc-5' AND plid IN(0,120,133,151,179,332,1630) ORDER BY p1, p2, p3, p4, p5, p6, p7, p8, p9; +----+-------------+-------+--------+-------------------------------------+------------------------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-------------------------------------+------------------------+---------+------+------+-----------------------------+ | 1 | SIMPLE | ml | range | menu_parents,menu_plid_expand_child | menu_plid_expand_child | 70 | NULL | 19 | Using where; Using filesort | | 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 767 | func | 1 | | +----+-------------+-------+--------+-------------------------------------+------------------------+---------+------+------+-----------------------------+ 2 rows in set (0.00 sec) **NOTE same as above mysql> EXPLAIN SELECT * FROM menu_links ml INNER JOIN menu_router m ON ml.router_path=m.path INNER JOIN book b on b.mlid=ml.mlid WHERE menu_name = 'book-toc-5' AND p1=120 AND p2=133 ORDER BY p1, p2, p3, p4, p5, p6, p7, p8, p9; +----+-------------+-------+--------+---------------------------------------------+--------------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------------------------------+--------------+---------+-------------------+------+-------------+ | 1 | SIMPLE | ml | ref | PRIMARY,menu_parents,menu_plid_expand_child | menu_parents | 74 | const,const,const | 111 | Using where | | 1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 767 | func | 1 | Using where | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | dp60.ml.mlid | 1 | | +----+-------------+-------+--------+---------------------------------------------+--------------+---------+-------------------+------+-------------+ 3 rows in set (0.00 sec)