1

I have a series of small 'attribute' tables (innodb) with this structure:

uuid 
name (TEXT)
organisation_id (INT)

In my application, when users add a new attribute, I am first performing a lookup if it already exists, SELECT * WHERE name = '?' AND organisation_id = '?'

What would the optimal index setup be?

Bonus:

I am also displaying all attributes from an organisation so often just querying the table by organisation_id, do I need a separate independent key on the organisation_id column or will the compound be ok for this too?

Improve this question

2 Answers 2

Reset to default
1

In MySQL, the order of the columns in an index is very important. Searching over an INT is faster than search on a string(VARCHAR). So an index on (organisation_id, name) would be better than that on (name, organisation_id).

If a WHERE clause satisfies the prefix an INDEX then that INDEX is used. So no need for a separate index on organisation_id. The existing index will take care of it.

Improve this answer
3
  • I agree on the index, but for a different reason. Major benefit with that index is that sometime organisation_id alone is used in predicate.
    – Lennart - Slava Ukraini
    Commented Apr 11, 2020 at 15:38
  • Wrong. Either order is equally good. Think of it this way: The two columns are concatenated, then that becomes the key.
    – Rick James
    Commented Apr 16, 2020 at 4:03
  • MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table. dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html
    – Subrata Das
    Commented Apr 16, 2020 at 12:08
1 
name (TEXT)

TEXT columns are not indexable. There is a way to do a prefix -- INDEX(name(30)), but it is rarely useful.

Instead, change it to a suitably sized VARCHAR, which can be indexed (up to some limit).

Once you change to VARCHAR, either of these will work equally well:

INDEX(name, organization_id)
INDEX(organization_id, name)

If you have other queries, they may guide you into which would be more useful.

"a lookup if it already exists" -- Consider whether IODKU would be appropriate.

More discussion here: https://stackoverflow.com/questions/50239658/higher-cardinality-column-first-in-an-index-when-involving-a-range

Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.