SphinxSE with sphinx engine in MariaDB is slow

General Discussions - English
1

Hello,

New to manticore while I have been using SphinxSE since 2007.

I was able to install manticore on my server and it works great especially using mysql -h0 -P9306. http works too. So I have 2 indexes and one works slowly with sphinx engine in MariaDB and the other one doesn’t work. Not sure why. So first how can I find out why it’s so slow and why the second index is not working. The first index is a delta + main and the one not working is single.

Is there a better way than using the sphinx Engine in MariaDB. I read I could used FEDERATED engine instead but MariaDB says it’s deprecated.

Also when I search with mysql it works super fast so I am not sure what is the issue.

Thanks in advance. All help appreciated.

2

So first how can I find out why it’s so slow

I’d first check in the query log. If it doesn’t reveal anything, I’d switch on the debug mode (see LOG_LEVEL here) and would check the searchd log.

3

Thank you! I am not sure why but it worked. It went from a 7s search and the other I didn’t know was working was taking 13 minutes. That’s why I thought it didn’t work because it was the smaller index. Any how. I added this to my.cnf file:

log_warnings=9
log_error= /var/log/mysql/mariadb.err

And it worked right away after restart. Search are now ms as it should. Thanks for the help.

Is there a better alternative to sphinxSE or it should keep using it? I mean since it’s working I am not sure why I should. I am thinking more about future compatibility.

What would be the best way to find the total amount of records for a search? Don’t need all the records just the number.

4

Is there a better alternative to sphinxSE or it should keep using it? I mean since it’s working I am not sure why I should. I am thinking more about future compatibility.

Using the FEDERATED engine may be a better alternative since I believe mysql/mariadb will keep supporting it in future versions and I’m not sure about SphinxSE.

What would be the best way to find the total amount of records for a search? Don’t need all the records just the number.

select count(*) from tbl where ... (query to Manticore).

1 Like
5

Thanks for the answers it’s really helpful.

MariaDB is now using a fork of FEDERATED named FEDERATEDX which should hopefully work the same.

I read the manual that says that we need to match the structure of the index. Is it mandatory?

So in my existing configuration I am using main + delta. I was not able to configure delta yet.

Just to text I created the following :slight_smile:
CREATE TABLE assets.manticore (
id int(10) unsigned NOT NULL,
year int(11) NOT NULL,
rating float DEFAULT NULL,
query varchar(1024) NOT NULL,
KEY query (query)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci CONNECTION=‘mysql://root@127.0.0.1:9306/pilot/assets.index’;

When I do the following search:

SELECT * FROM assets.manticore WHERE query=‘SELECT * FROM assets.index WHERE MATCH ('haircut')’;

it says: Received error: 1146 : Remote table does not exist where assets.index is my main index.

Right now I have assets table in which the elements are recorded and then I have an assets.sphinx table. Do I need to make my assets table the one linked to the index?

6

Hello,
Could you able to connect Federated database with Manticore. Also I couldn’t able to connect SphinxSE with Manticore, can you suggest what versions are you using for Manticore/MariaDB/SphinxSE which will be useful for others.
I used Sphinx & SphinxSE for recent years and new to Manticore.