2

I have a slight challenge with data that I need to get solved. I have a table where the plain text of emails is stored in a longtext column. It was like this before I started developing in the framework. There are over a million rows already with large emails stored in them.

I need to be able to search for text in that field but it takes ages before any results are returned. This is not ideal because there are hundreds of users using the system and they need the ability to search email content.

I have tried the following simple SQL statement but it takes too long to find records:
select id from emails where description like "%hello world%";

According the the MySQL documentation, full-text indexing can only be used in char, varchar and text fields.

Can anyone suggest an efficient way to search in a longtext column?

or

Are there any SQL methods / commands that can be used to run through the table and splitting the longtext field's content into multiple text columns for full-text indexing?

Kobus Beets
  • 29
  • 5

1 Answers1

1

I have tried the following simple SQL statement but it takes too long to find records: select id from emails where description like "%hello world%";

Unfortunately mysql cannot use indexes for %something% queries even on varchar fields. In fact it cannot use indexes on '%something' queries either. Indexes can be used only when the wild card occurs at the end of the string.

According the the MySQL documentation, full-text indexing can only be used in char, varchar and text fields.

I wish the documentation would be clearer here, this is speaking of TEXT family of fields. That means you can actually create an index on LONGTEXT and MEDIUMTEXT types of fields. Try this:

CREATE TABLE ltxt(bada LONGTEXT);
ALTER TABLE ltxt ADD FULLTEXT INDEX ftxt2(bada);

Yes, you will be pleasently surprised!

e4c5
  • 52,766
  • 11
  • 101
  • 134
  • I have already tried adding fulltext indexing on the table but it fails. Here's the output from another attempt on a newly created table. `create table sample(description longtext);` - `Query OK, 0 rows affected (0.20 sec)`. `alter table sample add fulltext index descidx(description);` - `ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes`. The engine that is used is: InnoDB. – Kobus Beets Jul 29 '16 at 07:30
  • I think I found my issue. I'm currently using mysql 5.5 and not 5.6. Fulltext indexing is only added to InnoDB from mysql 5.6 onwards. – Kobus Beets Jul 29 '16 at 07:41
  • Yes, that is correct luckily upgrading is a painless process – e4c5 Jul 29 '16 at 07:48
  • I am running the Debian OS. Someone mentioned that MySQL 5.6 is not available for it yet. Is this true or are there any steps to get it updated from 5.5 to 5.6? Once I've successfully updated MySQL and tested the search on the system, I will post the steps I took as the answer to my question. I am not a systems administrator and have limited knowledge with upgrading programs like MySQL. – Kobus Beets Jul 29 '16 at 11:26
  • all the best with your 5.7 installation. – e4c5 Jul 29 '16 at 11:47