CODEWITHSUNDEEP
mysqlinnodb
Zane
Zane

Reputation: 2870

Does size of an Text field in a MySQL InnoDB table affect performance when field not used in a query

I have an InnoDB table of about 500 000 rows which is growing and I am trying to improve select query performance.

The table is about 2GB big at the moment, most of it's size comes from a text field that stores quite a bit of xml data. This xml data is not read often and most queries don't select it.

Is there a chance that moving this data into a separate table would speed up the queries by allowing more rows to be held in the servers limited memory at once?

Upvotes: 1

Views: 810

Answers (1)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44333

If the table is InnoDB and you are not specifying the text field in SELECT, you should be OK. However, you should make sure the Primary Key is as small as possible (it is housed in the gen_clust_index). Keep in mind that InnoDB performs a clustered index lookup with every query, regardless of non-Indexed Lookups, Indexed Lookups, or Primary Key Lookups.

Notwithstanding, the fact that you asked this question shows that just the opposite still needs to be considered: There will be some queries that include Text Fields in the result set.

I would make sure of the followiing: MySQL Packets, InnoDB Log Files and InnoDB Log Buffer are sized properly. and Why be concerned with these settings? Rows of Data must pass through these structures. Make sure that max_allowed_packet is large enough to hold the biggest text field you have in the database at least 10 times. Same goes with InnoDB Log Files and the InnoDB Log Buffer.

Upvotes: 3

Related Questions