Hi again,
Provided table has many columns with “varchar(255)” . Since it uses “variable char”, it might happen that some rows are shorter than others.
On previous MySQL versions, you could create a table definition that potentially could surpass 8126 bytes (since at least 2 elements should be able to fit on a 16 kb page), but not get an error. On newer versions (upstream, Percona Server or MariaDB variant), you might be getting an error while trying to create a table definition and index that exceeds the threshold .
If using “text”, content will be stored off page and size limit will be larger so there shouldn’t be any problem with storing large values.
You previously said:
Our application can spend a lot of time running select queries looking for users based on those 64 fields in the WHERE clause. It might be running those select queries on a few hundred thousand users, and it might also run several hundred or thousand of those queries (over all users) at a time.
Even though the 64 fields allow 255 characters, in practice most of our clients are probably using between 10 to 30 fields, and the data in each one is at most a couple dozen characters, although there are certainly outliers.
and
Here is the table structure, obviously those 64 fields are causing this issue. My question is what are the implications for the various possible solutions. The application might run a lot of queries that use them, e.g. "SELECT * FROM users WHERE field1 = ‘…’ AND field2 LIKE ‘%…%’ AND field3 != ‘…’ etc.
Using filters such as “LIKE” , or comparing large values to a fixed string is costly. Using and index should improve performance, but with such large column values, it can be lengthy. Remember that an index is basically an additional structure with a copy of the column data, and each MySQL query will use at most 1 index (unless you use UNION or a merge occurs in which multiple query results are merged together). If you need to create an index for every possible combination of fields you need to filter, it will be expensive, and if no indexes at all, full table scan will occur, which will take longer the more rows exist in “users” table.
You should check how long it takes now with current number of users, and check how much performance degrades the more data it’s on the table, as degradation will occur the more rows you add.
if you need to manipulate a very high number of records that do not fit into a relational structure as it is MySQL that benefits from defined structure and use of “joins”, then you might consider migrating to nosql such as Mongo, that specializes in this kind of generic structures where different types and values are stored .
Regards