I have a news website where I store my articles in a table that looks like that:

Id   title   data

Where data contains the content of the article and is the biggest column in the database by far. The table currently takes 10 GB.

I have an issue that queries that search by content on this table take extremely long time as it is done by like clauses instead of full text.

My team told me that there was a full text index on the column, but it was too big and gave them hard time, even crashing the database.

What is the best way to deal with this issue?

  • 1
    What version of MySQL? What engine does the table use? It would be better if you added the SHOW CREATE TABLE tablename; output in the question. – ypercubeᵀᴹ Feb 10 '16 at 11:10
  • A full-text index crashing the "database", sounds weird by the way. What kind of crashes did happen? – ypercubeᵀᴹ Feb 10 '16 at 11:11
  • 5.6, and i think the index was too big. We talk about a 10 gb table, its innodb, the field is medium text – user69153 Feb 10 '16 at 15:34
  • @user69153 still the server is not supposed to crash in such case unless it is misconfigured (for example to use more memory than available) or you hit a bug – jkavalik Feb 11 '16 at 6:35
  • Tell your team that they shoyld let you create the FULTEXT index on a recent 5.6 version, or you will be forced to install java for Elasticsearch and they will have 2 problems :-) – jynus Nov 6 '18 at 20:15

Check firstly if the index fits into RAM.

If yes, check the table structure and the problematic query with DESCRIBE.

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.