CODEWITHSUNDEEP
mysqlsqlsphinx
Air In
Air In

Reputation: 975

Sphinx MySQL subquery not working properly

I'm working on a delta updating script and every time I try to use this command:

SELECT id, title, search
FROM related
WHERE id >
(
   SELECT max_doc_id
   FROM sphinx_delta
   WHERE counter_id = 2
)

Running that in phpMyAdmin would return 10 results.

SELECT id, title, search
FROM related
WHERE id > 1010

Running that in phpMyAdmin would return the SAME 10 results. Of course the value returned from the sub select, is 1010.

The only issue is, when I use the first query in Sphinx search, it returns 0 results, while the second one returns 10 results. I have no idea what's wrong, both queries work perfectly in MySQL, and I used the tutorial on Sphinxs website to make the config file.

Here's the portion from my config file:

    source delta2 : related
    {
            type        = mysql

            sql_host    = localhost
            sql_user    = *
            sql_pass    = *
            sql_db      = base
            sql_port    = 3306

            sql_query   = SELECT id, title, search FROM related WHERE id > (SELECT max_doc_id FROM sphinx_delta WHERE counter_id = 2)
    }

I tried posting this on Sphinx's forum, but got 0 responses. Any suggestions, I migrated from 0.9.9 to the latest version hoping it would fix it, it wasn't able to fix it.

Upvotes: 0

Views: 545

Answers (1)

chx
chx

Reputation: 11790

You are updating the delta while trying to read from the ... delta? sphinx doesnt update indexes, it recreates them, there is nothing to read from.

Upvotes: 0

Related Questions