StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>1) Queries against the TEXT table were always 3 times slower than those against the VARCHAR table (averages: 0.10 seconds for the VARCHAR table, 0.29 seconds for the TEXT table). The difference is 100% repeatable.</strong> </p> <pre><code>CREATE TABLE varcharTable (a varchar(255) NOT NULL, PRIMARY KEY (a)) ENGINE=MyISAM; CREATE TABLE textTable (a text NOT NULL, PRIMARY KEY (a(255))) ENGINE=MyISAM; mysql> explain SELECT SQL_NO_CACHE count(*) from varcharTable where a LIKE "n%"; +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | varcharTable | range | PRIMARY | PRIMARY | 257 | NULL | 5882 | Using where; Using index | +----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain SELECT SQL_NO_CACHE count(*) from T where a LIKE "n%"; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | T | range | PRIMARY | PRIMARY | 257 | NULL | 5882 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) Index is being used for the VARCHAR table, but not for the TEXT table (in the Extra column) </code></pre> <p><strong>2) search is not required on comments table.So, querying is not required and since its long too. Its type is preferred to be <code>text</code> And then since its text you cannot search on it</strong> .So, put the comments(non-searchable and affecting performance) and replies on separate table. So, that the replies table will function good and the comments table will be kept just for storage purpose, no search performed on them.</p> <p><strong>Conclusion:</strong> So, put them the Comments table in a separate table.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload