1

I have a table user_list

CREATE TABLE IF NOT EXISTS `user_list` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `email` varchar(40) NOT NULL,
  `userdata` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1000265 ;

The queries I perform on this table are

  • SELECT email WHERE id = XXXX
  • SELECT userdata WHERE id = XXXX
  • SELECT id WHERE username = XXXX
  • UPDATE user_list SET userdata WHERE id = XXXX (used pretty frequently)
  • UPDATE user_list SET email WHERE id = XXXX (These are the only I queries I am going to perform on this table)

Now the problem is userdata field stores all the information about the user and is relatively large.Its text generally contains about 2000 characters. Say I am going to insert some 10 million+ entries in the database.

Does the size of this userdata field affect the performance of the queries I mentioned above?

If I store the userdata text as a separate file and just store the path in the database, will there be a significant performance difference? Since queries 1,3 and 5 do not involve the userdata field, will their performances be increased significantly since I have removed the userdata field from the table?

Is having a separate table user_data_list with the fields for the userdata 

----------id----------userdata-----------------

provide any advantages over how it is now? Does having such a separate table have any disadvantages?

Improve this question
1
  • Do you want an complex answer or basic one..? because lots off factors needs to be accounted for like Disk i/O and an explainment how MyISAM engine handles indexing.. – Raymond Nijland Dec 7 '13 at 15:46
1

A short answer would be: Yes, it affects performance.

A database stores it's data inside formatted database blocks. These blocks are the basis of all caching mechanism, so caches will store database blocks. Querying the database will move some read blocks into the query cache.

You can imagine, the smaller a block is, the more blocks can stay safely inside the caches without having to drop them for new blocks from new queries.

However, I'd say that in many scenarios that store data this way, performance differences of the kind I described might not be the most prominent factor for the underlying system. A benchmark might find differences, but for a system to react noticeably faster you need at least 10% faster responses (the number differs from study to study), and I'm not certain excluding the text column to it's own table will give you that.

On the other hand, if the column would be indexed, that might make matters different. But as I see no index other than the primary key, I see no reason to refactor here - other than that such a column is considered bad practice.

Improve this answer
6
  • I thought indexing is useful only when searching by that column using WHERE.. Isn't that the case? If I index the userdata column, what would be the pros and cons? – SatheeshJM Nov 27 '13 at 9:45
  • For the sample queries above, there's no added benefit of indexing the column. Rather to the opposite, inserts and updates would need to update the index alongside, which would make performance worse. Indexing has usually two parts. One is building the index tree, the other is adding an optional constraint to a record set. A unique index adds the index tree and adds the constraint of uniqueness to the chosen column(s). – 0xCAFEBABE Nov 27 '13 at 9:51
  • Those are not sample queries. Those are the only queries this table will encounter.. So I am guessing indexing won't be of much help here.. One final question : you are saying I'm not certain excluding the text column to it's own table will give you that. Since queries 1,3 and 5 do not involve the userdata field, will their performances be increased significantly if I remove the userdata field from the table? – SatheeshJM Nov 27 '13 at 9:56
  • @SatheeshJM actually indexing is only usefull when the MySQL optimizer sees it can use an index when the execution cost is better or it can norrow down the rows MySQL needs to analyse.. if the index is causing more random disk i/O lookups very expensive an full table scan (sequential scan) will be preferred because this is pretty cheap.. index selectivity is also playing an great role in this... – Raymond Nijland Dec 7 '13 at 16:10
  • @SatheeshJM i would like to advise you to not trust blindy on the answer and comments from an programmer when databases are involved (0xCAFEBABE this is not meant to insuit you in any way) because most programmers most likely dont fully understand the MySQL internals (the way MySQL is working), i know this because iam also an programmer and iam seeing this "trend" under the programmers (SatheeshJM i know this sounds contradictory but i advise you to trust me on this one)... – Raymond Nijland Dec 7 '13 at 16:15

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.