Optimizing AjaxSearch When It Has to Filter by Many TV Fields

A real-world implementation of this pattern included a huge SQL query generated for an AjaxSearch-style real-estate filter. The important lesson was not the exact table names. It was the architecture problem: when a search has to combine many template variables, each filter often becomes another join or subquery against site_tmplvar_contentvalues.

Why this gets slow

TV storage is flexible, but it is not the same thing as a purpose-built indexed search table. Once a catalog needs price ranges, property type, bedroom count, location, and multiple sale/rent fields at the same time, the SQL quickly grows into a large join chain.

Typical symptoms

• the query works, but gets harder to maintain every month
• the same resource is joined against TV data many times
• small filter changes require editing SQL-like configuration instead of template logic
• query speed degrades as the catalog grows

Better options

If the project is already committed to TVs, reduce duplication first: normalize field usage, avoid storing the same meaning in multiple TVs, and remove filters nobody actually uses. If the project is expected to keep growing, it is often better to build a dedicated index or derivative table for search and filtering rather than asking AjaxSearch to reconstruct all business meaning from raw TV rows every request.

Rule of thumb

AjaxSearch is a delivery layer, not a substitute for a search index. The moment your SQL starts looking like a report builder, it is time to simplify the data model or add a prepared search layer.