What will be the difference in query performance regarding
@Table(name = "users", indexes = {
@Index(columnList = "code", name = "code"),
@Index(columnList = "current_city", name = "cbplayer_current_city_hidx")
})
vs.
@Table(name = "users", indexes = {
@Index(columnList = "code, current_city", name = "both"),
})
Will both of these just create a b-tree for code and a b-tree for current city? Or will the second one create some kind of b-tree involving both?
The first one seems more flexible where I can query on code and later query on code/current_city later as my system evolves without needing to do DBA work(KISS until need even better performance).
I am really just curious on the difference here.
To see what that generates, use the commandline tool mysql and use the SHOW CREATE TABLE command.
When is the composite INDEX(a,bb) useful? When are separate INDEX(a), INDEX(bb) useful? Some examples
WHERE a = 'xyz' -- INDEX(a) or INDEX(a,bb)
WHERE a = 'xyz' AND bb = 123 -- INDEX(a,bb) or INDEX(bb,a); others are less good
WHERE a = 'xyz' AND bb >= 123 -- INDEX(a,bb); others are less good
WHERE a >= 'xyz' AND bb = 123 -- INDEX(bb,a) is best
WHERE bb = 123 -- INDEX(bb) or INDEX(bb,a)
That is, the optimal index depends on what the important queries are.
On the other hand, if the table has only a hundred rows, you are unlikely to notice any performance issues if you don't have any indexes.
Another discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql