1. Create the table, the storage engine is MyISAM, and use the MD5 function to create a hash value for the large text field blob
CREATE TABLE t2 (ID varchar, content blob, Hash_value varchar) engine=myisam default Charset=utf8;
2. Inserting data
INSERT into T2 values (1, repeat (' world1 '), MD5 (content));
INSERT into T2 values (2, repeat (' world2 ',), MD5 (content));
INSERT into T2 values (3, repeat (' world3 ',), MD5 (content));
INSERT into T2 values (4, repeat (' world4 ',), MD5 (content));
INSERT into T2 values (5, repeat (' World5 ', MB), MD5 (content));
3. Use hash value query, only for exact match
SELECT * FROM T2 where HASH_VALUE=MD5 (repeat (' world4 ', 70));
Using a hash value query is faster than using a BLOB text field query, because using a hash value matches a much shorter length.
4. To make a fuzzy query, you can prefix the top n columns of a BLOB field
Create index Idx_blob on T2 (content (150));
Prefix index of the first 150 bytes of the field Content field in the T2 table
5. Use explain or desc to view execution plans, use indexes
Mysql> explain select * from T2 where the content like ' world4% ';
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | Simple | T2 | Range | Idx_blob | Idx_blob | 153 | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in Set (0.01 sec)
Mysql> desc SELECT * FROM T2 where the content like ' world4% ';
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | Simple | T2 | Range | Idx_blob | Idx_blob | 153 | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in Set (0.00 sec)
As you can see, the prefix index Idx_blob is used.