MySQL index usage (self_and_descendants, descendants, self_and_ancestors, ancestors)

[adz624]

I have below indexes:

• index_agents_on_lft
• index_agents_on_rgt

And i use explain to check those methods, and found out some problems with mysql index usage:

1. self_and_descendants will make a between search with lft. but mysql will not use index_agents_on_lft index by default and full-table search instead.
2. descendants is like self_and_descendants method, but more condition with primary key search id != ?, and mysql will use primary key default for this query (very bad performance)
3. self_and_ancestors also like 1. will not use lft
4. ancestors like 2. primary key by default.

After my test, best way is create a index index_agents_on_lft_and_rgt and force index for those queries.

I have implemented that in my project (override those methods), I think awesome_nested_set may provide a way to make user setup the index like below:

# in model define
acts_as_nested_set force_index: :index_agents_on_lft_and_rgt

But this gem is not only for mysql, considering other database, maybe we can provide mysql version first.

What do you guys think? should i send a pull request?

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.