3

The question is whether MySQL can use separate physical storage for strings transparently, but with the same operations that are applicable to varchar, including %.

I have a large number of duplicated strings in my MySQL database: for example, many customers have the same first name. To save space, especially in indexes, and thus speedup the queries, I would prefer to physically store only an ID and store the actual string elsewhere: instead of varchar(30)s

| Mary | Smith  | New York |
| Mary | Cooper | Chicago  |
| John | Cooper | New York |

I want to only store ints

| 1 | 2 | 3 |
| 1 | 4 | 5 |
| 6 | 4 | 3 |

where the IDs refer to the strings:

| 1 | Mary     |
| 2 | Smith    |
| 3 | New York |

I can do it manually and use joins to map between strings and IDs in each query, but my question is whether there is any property of a field to tell MySQL to handle this transparently for me, so that in the queries I operate with the table as if it contained strings but the underlying physical storage for strings would be separate?

In my case the DB is rarely (if any) updated but frequently read, the queries being rather complex and slow, so I need to optimize for select, not for update.

3

MySQL has no such feature.

It is left to the user to "normalize" the data either for avoiding having to update multiple spots and/or for saving space.

In your example, it is generally not practical to do such with first/last name. But it may be advisable for "locations".

It is not practical to dedup names (for example) because the payoff is poor. With "Mary" sitting somewhere else, the code has to implicitly (as you hypothecate) or explicitly (via a JOIN) reach for the string. In large datasets, this is likely to cause an extra disk hit -- costly. Also, "Mary" is 4 characters; the first_name_id might be a MEDIUMINT UNSIGNED, which is 3 bytes -- not much savings. For bigger strings that repeat a lot (company names), the tradeoffs might be better.

The main purpose of "normalization" is to put things in a single location. A company name should be spelled out only once in a system that talks about companies. In its place an id would be used -- possibly an integer, possibly a ticker symbol (as in a Stock database). When the company changes its name, only one spot needs to be changed. (If the ticker changes, as happened with AOL, then the code is messy.)

  • Thank you! Why is it not practical to do with first names? Why not apply it to all character strings in the whole database: use one large table to store all strings and in all other places only store ids? (Especially if my DB is rarely or never updated.) – Alexander Gelbukh Mar 21 '15 at 0:51
  • And on a related note: is there some another DBMS, equally easy to use with Apache + PHP, that does this and would be advisable instead of MySQL? (You can update your answer with this, to make it even better.) – Alexander Gelbukh Mar 21 '15 at 0:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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