将含布尔搜索与双关联的MySQL查询改写为Sphinx查询以提效

Alright, let's walk through converting your MySQL query—complete with boolean search, joins, and geographic distance calculation—into a Sphinx-friendly setup that'll give you the performance boost you're after. Here's how to tackle it step by step:

1. Update Your Sphinx Index Configuration First #

Sphinx works best with pre-indexed data—it can't perform real-time joins like MySQL, so you need to pull all the data you need (including joined fields) into your index upfront.

Your original Sphinx sql_query only fetches post basics, so let's expand it to include the geographic fields and any other joined data you need (since you mentioned two JOINs):

sql_query = \
  SELECT p.id, p.title, p.description, l.Latitude, l.Longitude \
  FROM post p \
  JOIN location l ON p.location_id = l.id \
  -- Add your second JOIN here (e.g., JOIN category c ON p.category_id = c.id) \
  -- Include any fields from the second join you need for filtering/sorting

Next, define the geographic fields as Sphinx attributes so you can use them for distance calculations and filtering:

# Store latitude/longitude as float attributes for geographic operations
sql_attr_float = Latitude
sql_attr_float = Longitude

If you have other joined fields (like category IDs), define those as attributes too (e.g., sql_attr_int = category_id).

2. Handle Boolean Full-Text Search in Sphinx #

Good news: Sphinx natively supports boolean search syntax, and it's faster than MySQL's IN BOOLEAN MODE. Your polopet* prefix search works directly in Sphinx's MATCH() clause—no extra keywords needed.

For example, a basic boolean search would look like:

MATCH('polopet*')

You can also add other boolean operators (like +polopet -cat for "must include polopet, exclude cat") just like in MySQL.

3. Calculate Geographic Distance Efficiently #

Sphinx has a built-in GEODIST() function that calculates distance between two coordinates way faster than manual SQRT()/POW() calculations. It returns distance in miles by default; multiply by 1.60934 if you need kilometers.

Instead of your custom formula, use:

GEODIST(Latitude, Longitude, 48.711982, 2.677415) AS distance

If you really need to stick to your original formula, you can still write it in the Sphinx query—but GEODIST() is optimized and cleaner.

4. Full Sphinx Query Example #

Putting it all together, here's your converted query. Note that Sphinx uses slightly different syntax for scoring and sorting:

SELECT
  id,
  WEIGHT() AS score, -- Equivalent to MySQL's AGAINST() score (Sphinx's built-in match weight)
  GEODIST(Latitude, Longitude, 48.711982, 2.677415) AS distance
FROM your_sphinx_index_name -- Replace with your actual index name
WHERE MATCH('polopet*')
-- If you need to filter by joined fields (e.g., category), add it here: AND category_id = 5
ORDER BY score DESC, distance ASC
LIMIT 100; -- Always use LIMIT to avoid fetching unnecessary results

You won't need DISTINCT here unless your index has duplicate id entries—Sphinx indexes unique records by default, so DISTINCT is usually redundant.

5. Quick Performance Tips #

• Use real-time (RT) indexes if you need to handle frequent post updates—they let you insert/update data without full index rebuilds.
• For large datasets, split your index into smaller pieces (delta + main indexes) to reduce rebuild time.
• Use SET SORT_MODE=SPH_SORT_EXTENDED if you need more complex sorting rules (e.g., ORDER BY @weight DESC, distance ASC).

内容的提问来源于stack exchange，提问作者Олег Гаврилів