0

So I have a table where we store a sequence value of what we call a "tree". Sort of looks like this

1
1.1
1.1.1
1.1.2
2
2.1
2.2
3
3.1
3.1.1
3.1.1.1
3.1.1.2
3.1.1.3
etc
etc
etc

Our application searches this tree, rebuilds the tree. Performs operations on it. What I am asking is if it would make sense to make a functional index on this column? If so, what would be ideal? Each number within the sequence? Cast it to number from the string? Any thoughts or idea would be much appreciated.

Improve this question
7
  • I don't think MySQL has any functions that do things with strings like this, except SUBSTRING_INDEX(). I can't think of how you would create a useful index on them. Commented Mar 26, 2024 at 17:18
  • It might be better to create a hierarchical table, where 1.1 points to 1 as its parent. Commented Mar 26, 2024 at 17:20
  • 1
    What types of searches are you doing? The index design may or may not help, depending on the specific query. Commented Mar 26, 2024 at 17:25
  • 1
    Our application searches this tree Searching criteria pattern? rebuilds the tree Details needed. Performs operations on it. What operations? Commented Mar 26, 2024 at 17:43
  • @Barmar - Can you please elaborate further on hierarchical table? Commented Mar 26, 2024 at 18:09
Related questions
581
What is the most efficient way to parse a flat table into a tree?
921
MySQL: Large VARCHAR vs. TEXT?
893
Multiple Indexes vs Multi-Column Indexes
Load 5 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.