Sphinx query convert from mysql

Question

I have this working SQL query with boolean search mode and two joins, and need to rewrite it to work with my index query in sphinx for better performance.

sql query in sphinx config:

 sql_query     = \
      SELECT id, title, description \
      FROM post

Sql query for search:

 Select distinct p.id, 
     MATCH (title, description) AGAINST ('polopet*' IN BOOLEAN MODE ) AS score , 
     SQRT( POW(69.1 * (l.Latitude - 48.711982), 2) + POW(69.1 * (2.677415 - l.Longitude) * COS(l.Latitude / 57.3), 2)) AS distance 
 FROM post p 
     join location l on (l.id = p.location_id and acos(sin(l.Latitude * 0.0175) * sin(48.711982 * 0.0175) + cos(l.Latitude * 0.0175) * cos(48.711982 * 0.0175) * cos((2.677415 * 0.0175) - (l.Longitude * 0.0175))) * 3959 <= 31.5) 
     right join post_category as pc on ( p.id = (
         select distinct post_id 
             from post_category as pc 
             where pc.post_category in ("BUSINESS_SERVICES") and post_id=p.id
     )) 
 ORDER BY score Desc 
 LIMIT 30 OFFSET 0 ;

Can somebody help me?

Answer 1

This may be not a complete solution for your task, but should be a good start:

Config:

[snikolaev@dev01 ~]$ cat sphinx_49576229.conf
source min
{
    type = mysql
    sql_host = localhost
    sql_user = root
    sql_pass =
    sql_db = test
    sql_query = select  p.id,  p.title, p.description, l.Latitude, l.Longitude FROM post p join location l on l.id = p.location_id
    sql_attr_multi = uint post_category_id from query; SELECT post_id, id from post_category
    sql_attr_float = Latitude
    sql_attr_float = Longitude
}

index idx_min
{
    path = idx_min
    source = min
    min_prefix_len = 3
}

searchd
{
    listen                       = 9314:mysql41
        log = sphinx_min.log
        pid_file = sphinx_min.pid
    binlog_path = binlog
}

Indexing:

[snikolaev@dev01 ~]$ indexer -c sphinx_49576229.conf --all --rotate
Manticore 2.6.1 9a706b4@180119 dev
Copyright (c) 2001-2016, Andrew Aksyonoff
Copyright (c) 2008-2016, Sphinx Technologies Inc (http://sphinxsearch.com)
Copyright (c) 2017-2018, Manticore Software LTD (http://manticoresearch.com)

using config file 'sphinx_49576229.conf'...
indexing index 'idx_min'...
collected 1 docs, 0.0 MB
collected 2 attr values
sorted 0.0 Mvalues, 100.0% done
sorted 0.0 Mhits, 100.0% done
total 1 docs, 16 bytes
total 0.005 sec, 3152 bytes/sec, 197.00 docs/sec
total 6 reads, 0.000 sec, 10.6 kb/call avg, 0.0 msec/call avg
total 14 writes, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
rotating indices: successfully sent SIGHUP to searchd (pid=21241).

Searching:

[snikolaev@dev01 ~]$ mysql -P9314 -h0
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 2.6.3 5bc9e81@180316 dev

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select *, geodist(48.712002, 2.677411, latitude, longitude) dist from idx_min where match('desc*') and dist < 20 and ANY(post_category_id) in (1);
+------+-----------+-----------+------------------+-----------+
| id   | latitude  | longitude | post_category_id | dist      |
+------+-----------+-----------+------------------+-----------+
|    1 | 48.712002 |  2.677410 | 1,2              | 11.099242 |
+------+-----------+-----------+------------------+-----------+
1 row in set (0.00 sec)