0

I removed records from a table using a Mysql Editor.

This table was previously indexed by Sphinx (using indexer --rotate).

When I re-rotate again using indexer --rotate the old record can still be queried.

To test whether the index is actually being obeyed I

  • Added a new record
  • Modified an old one

I re-rotated and the new/modified records were as expected yet the deleted record was still being found during a SphinxQL query.

Lastly, I added a Where condition to the indexing so it would only index those records where Add=1.

I then added back the old record with its original ID and and set Add=0.

NOW the record is not found during SphinxQL query.

I've read various solutions in the interim such as having a secondary 'Deleted Records' table the index can query yet this seems like a difficult and error-prone solution.

Isn't there a simpler way to have Sphinx simply re-create the index with only the existing records?

Since I do a daily rotation would deleting the index and then rotating it work? I'd test it but since Sphinx is so finicky would prefer to ask before breaking anything.

user3649739
  • 1,829
  • 2
  • 18
  • 28
  • Franky (looking at the post edited question!) - think it was just a mistake with testing. Suggest you try the same procedure again, and wouldnt be surprised you find it works. A --rotate on the index, completely rebuilds the index from scratch, with no reference to the old one. If was still visible, maybe you accidentally viewing a cached query? The fact that filtering it with Add=0 rather than delete, confirmed the row would be gone from sphinx. – barryhunter Feb 24 '17 at 18:56
  • @barryhunter I tried that several times. If I rotate with new records those can be queried so I know that the new records are being correctly rotated using `--rotate`. However no matter how many times I change/add records and rotate and see them succesfully queried that old record is still there (id, name, etc). The fact that adding it back (wit the original id I deleted) WITH Add=0 makes it disappear leads me to believe it is not in fact being deleted but is obeying the Add=0 somehow. I'll re-retest and report back but so far that is what is occuring. – user3649739 Feb 24 '17 at 23:40

0 Answers0