[rt-users] ticket content not displaying via RT's web 'Search' but shows up with sphinx's 'search' binary

[Thomas Sibley]

On 02/07/2013 03:13 AM, Subin wrote:
>> .... the second query isn't run when the first returns 0.
> This hint helped narrow down things. I figured out the pattern/situation
> under which search failure occurs, which is:
> 
> "If I create/update a ticket and immediately perform fulltext: search
> of the same content before reindexing is run(on delta), it will fail to
> show up on fulltext searches even after the content is later
> reindexed. In this situation if I run a 'search' from CLI(on delta index
> specifically) it returns successful hits. Running SELECT COUNT by hand
> also returns >0 integer at this point. If I do not search for newly created
> ticket-content before its reindexed, this won't occur. Things are normal
> in that case."

Thanks for your detailed investigation and description (which I've
trimmed from below).  It sounds very much like you're hitting a cache on
the SELECT COUNT query that isn't expired until you create a new ticket
(or presumably after some longer amount of time).  RT doesn't cache
database queries this way, so I suspect it's MySQL's standard query
cache or, perhaps more likely, some cache in the Sphinx engine for MySQL
(not the sphinxd server itself).  Modifying one of the tables involved
in the query, as you do by creating a new ticket or replying to an
existing ticket, then triggers the cache expiry.

If you're interested in further tracking this down, I'd investigate
caching at the MySQL and Sphinx level.  However, it may be moot in a
production environment where the caching is beneficial and there's
enough database activity to keep it from being noticeably stale.