CODEWITHSUNDEEP
sqlperformancemysql
artvolk
artvolk

Reputation: 9528

Effectively (in terms of performance) store and use set values in MySQL

I need to store set value in MySQL column. I completely like the build-in SET type, but it seems that FIND_IN_SET() function requires table scan which is bad.

It seems that SET uses binary values under the hood. If I use binary value to represent a set, for example for a set of four elements it could be something like this:

0100 0101 0110 etc

How I can store it (which type), how to query and take advantage of indexes?

P.S. I indeed need a solution without separate linking table, something similar to SET.

Thanks in advance!

Upvotes: 1

Views: 82

Answers (1)

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25390

You can use BIT field type and use Bit functions for quering, however you will need hardcode somewhere yours set values in binary form. Other disadvantage that you can have only 64 possible values.

Upvotes: 1

Related Questions