Skip to content

How to overcome MySQL's group_concat_max_len limit in Thinking Sphinx

Fixing truncated Thinking Sphinx indexes caused by MySQL's default GROUP_CONCAT limit.

Published
Reading time
2 min read

I recently discovered some strange search behavior in a project that used Thinking Sphinx. A document had many pages, each with a plain_text column that needed to be searchable through both the Page and Document models.

Here is the relevant part of the Thinking Sphinx declaration:

define_index do  
  # ...
  indexes :title, :sortable => true
  indexes :author, :sortable => true
  indexes pages.plain_text, :as => :plain_text
  # ...
end

Documents in this database generally contained between 300 and 500 pages. Thinking Sphinx used MySQL’s GROUP_CONCAT()1 function to join their columns, but MySQL limited the result to 1,024 bytes by default.2 That limit was far too small for hundreds of pages.

As a result, Thinking Sphinx indexed only the first pages of each document. Our tests had missed the problem because the test document contained only four pages. The lesson was simple: test with realistic production-scale data at least once.

The solution

Add the following property to the index definition for any model that joins large columns:

define_index do
  # ...
  set_property :group_concat_max_len => 16.megabytes
end

After changing the property, reconfigure and rebuild the Sphinx index.

1 http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

2 http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_group_concat_max_len