Why is sphinx slower than mysql fulltext?

Question

Hello, I'm transferring my site search to sphinxsearch, now it's running on mysql fulltext.

I downloaded and installed the latest version of sphinxsearch from the official site.

I get terrible time results (sphinxsearch is slower). Here I created a test environment for search (works on the same dataset):
https://izap24.ru/sphinx/test.php

To get results, you can search for car brands, such as "bmw e46".

Did I configure the sphinx wrong?

Comments

65 ms Sphinx vs 2.45 s Mysql. Where is slower?

---

Ah, this is on warm data. On cold data, it takes the same amount of time.

---

Alexei Ukolov Yes, a second request will speed up many times over.

---

Everything is on the same server. Bases mySql and indexes Sphinx on different disks, both SSD. If you need any more data I will provide.

I use these settings:

source src1
{
	type			= mysql

	sql_host		= localhost
	sql_user		= 
	sql_pass		= 
	sql_db			= 
	sql_port		= 3306	# optional, default is 3306

     # Установим кодировку для работы с БД
        sql_query_pre = SET NAMES utf8
        sql_query_pre = SET CHARACTER SET utf8

	sql_query		= \
		 SELECT prod_search_index.id, prod_search_index.shop, prod_search_index.category, prod_search_index.prise, prod_search_index.brand,prod_search_index.name_ru,prod_search_index.active,prod_search_index.moto, \
		 IF(`prod_search_index`.`shop`!=1,1 , 0) AS `belbd`\
		 FROM prod_search_index \

	sql_attr_uint		= shop
	sql_attr_uint		= category
	sql_attr_uint		= prise
	sql_attr_uint		= brand
	sql_attr_uint		= active
	sql_attr_uint		= moto
	sql_attr_uint       = belbd
	sql_attr_multi      = uint folder from ranged-query; \
                 SELECT `prod`, `folder` \
                FROM `in_folders` \
               WHERE `id`>=$start AND `id`<$end; \
                SELECT MIN(`id`), MAX(`id`) FROM `in_folders` 
	
	#sql_ranged_throttle = 200
}

index izap_prod_search_index
{
	source			= src1
	path			= /var/www/siriusit/data/sphinxdata/indexes/izap_prod_search_index
	mlock = 0
	morphology = stem_ru
    min_word_len = 1
}

index izap_prod_search_index_rt
{
	type			= rt
	rt_mem_limit	= 128M
	path			= /var/www/siriusit/data/sphinxdata/indexes/izap_prod_search_index_rt
	rt_field		= name_ru
	rt_attr_uint		= shop
	rt_attr_uint		= category
	rt_attr_uint		= prise
	rt_attr_uint		= brand
	rt_attr_uint		= active
	rt_attr_uint		= moto
	rt_attr_uint		= belbd	
}

index izap_prod_search
{
  type = distributed
  local = izap_prod_search_index
  local = izap_prod_search_index_rt
}

indexer
{
	mem_limit		= 128M
}

searchd
{
	listen			= 9312
	listen			= 9306:mysql41
	log			= /var/www/siriusit/data/sphinxdata/logs/searchd.log
	query_log		= /var/www/siriusit/data/sphinxdata/logs/query.log
	read_timeout		= 5
	max_children		= 30
	pid_file		= /var/www/siriusit/data/sphinxdata/searchd.pid
	seamless_rotate		= 1
	preopen_indexes		= 1
	unlink_old		= 1
	workers			= threads # for RT to work
	binlog_path		= /var/www/siriusit/data/sphinxdata/binlogs
}

Answer 1

In general, I put the same query on the cron and measured the time in ms for mysql and sphinx.
The results were unstable and ambiguous:
2020-07-13 00:00:07: 242|5743
2020-07-13 01:00:09: 1795|5827
2020-07-13 02:00:04: 201|2086
2020-07-13 03:00:04: 119|3124
2020-07-13 04:00:06: 2807|1685
2020-07-13 05:06:27: 1153|384722
2020-07-13 06:00:05: 1114|2317
2020-07-13 07:00:07: 2719|3121
2020-07-13 08:00:05: 1699|2627
2020-07-13 09:00:17: 1575|12711
2020-07-13 10:00:03: 115|1954
2020-07-13 11:00:06: 1188|3142
2020-07-13 12:00:03: 639|1922
2020-07-13 13:00:04: 859|2369
2020-07-13 14:00:05: 1206|2813
2020-07-13 15:00:04: 273|2263
2020-07-13 16:00:04: 124|1901
2020-07-13 17:00:12: 1305|9818
2020-07-13 18:00:05: 924|3223
2020-07-13 19:00:15: 256|12140
2020-07-13 20:00:11: 1674|2470
2020-07-13 21:00:02: 500|1184
2020-07-13 22:00:03: 63|1707
2020-07-13 23:00:04: 711|2129
Then rolled out the new engine on the site and oh well:

2020-07-16 00:00:04: 33|2362
2020-07-16 01:00:03: 77|2029
2020-07-16 02:00:04: 64|2280
2020-07-16 03:00:03: 21|2091
2020-07-16 04:00:03: 22|1555
2020-07-16 05:05:52: 71|350448
2020-07-16 06:00:06: 85|4421
2020-07-16 07:00:03: 72|2492
2020-07-16 08:00:07: 72|5772
2020-07-16 09:00:04: 69|2436
2020-07-16 10:00:03: 32|1510
2020-07-16 11:00:03: 23|2007
2020-07-16 12:00:04: 54|1902
2020-07-16 13:00:03: 71|2213
2020-07-16 14:00:03: 31|2423
2020-07-16 15:00:03: 72|1581
2020-07-16 16:00:04: 24|2300
2020-07-16 17:00:07: 41|6524
2020-07-16 18:00:03: 38|1935
2020-07-16 19:00:03: 60|2060
2020-07-16 20:00:03: 32|1891
2020-07-16 21:00:03: 22|2277
2020-07-16 22:00:04: 22|2355
2020-07-16 23:00:03: 33|2003

Apparently the engine itself must be warmed up to work well)

Answer 2

Wrong set up or wrong search
sphinx is always an order of two faster than a muscle in a search

Comments

The default settings are mostly. Now I removed the definition of rt indices and paralleled my test environment, which in mysql and sphinx simultaneously searched (after all, the server is under load).

As a result, I get almost consistently much better performance from sphinx

Answer 3

Did I configure the sphinx wrong?

How should we know? Did you show us the settings? Or did you describe the server? Maybe you have them on different servers. Maybe you process the received data differently? Maybe your query itself is a bit messed up?

Comments

Clarified my question. In the case of Mysql, the data are selected. In the case of Sphinx I only get the id.