I use PHP library for sphinx. Wrote several sources in the sphinx.conf configuration (articles, news, notes).

After a successful search, I get an output array in PHP with the keys (IDs) of the rows found from each table (news, articles, messages).

Then I need to create a separate query for these obtained ID for each of the tables? To display the result to the user?

But if the output array will contain more than 1000 different identifiers? Will it be 1000 queries to the database? Many thanks for any comment or explanation!

One of the sources is described as follows:

source users : lsParentSource { sql_query_range = SELECT MIN(idDetailToUsers), MAX(idDetailToUsers) FROM detailtousers sql_range_step = 128 sql_query = SELECT idDetailToUsers as id, 9 as type, UsersTypeAccount, idDetailToUsers, SpecializationName, DetailToUsersName, DetailToUsersPhoto, city, country FROM detailtousers join users ON users.idUsers = detailtousers.idDetailToUsers left join usersspecialization ON usersspecialization.UsersSpecializationIdUser = detailtousers.idDetailToUsers left join specializationtousers ON specializationtousers.idSpecialization = usersspecialization.UsersSpecializationIdSpecialization WHERE idDetailToUsers >= $start AND idDetailToUsers <= $end GROUP BY idDetailToUsers sql_attr_uint = type sql_attr_uint = idDetailToUsers sql_attr_uint = UsersTypeAccount sql_field_string = SpecializationName sql_field_string = DetailToUsersName sql_field_string = DetailToUsersPhoto sql_attr_uint = city sql_attr_uint = country sql_query_info = SELECT idDetailToUsers, DetailToUsersName, UsersTypeAccount, SpecializationName, DetailToUsersPhoto, city, country \ FROM detailtousers WHERE idDetailToUsers = $id sql_ranged_throttle = 0 }

If you specify by your example, then in the output array I get the name of the type: field type:

  ["fields"]=> array(4) { [0]=> string(4) "type" [1]=> string(18) "specializationname" [2]=> string(17) "detailtousersname" [3]=> string(18) "detailtousersphoto" }

But without type (pointing to the table) Articles

I use the extension for PHP (PECL) Sphinx.

Code:

$ s = new SphinxClient; $ s-> setServer ("localhost", 9312);

 $s->setMaxQueryTime(9000); $s->setMatchMode(SPH_MATCH_ANY); //$s->setFilter('type', array( 9, 10) ); $result = $s->query($_POST['search'], '*'); var_dump($result);
  • @Oleg Ponomarchuk See Update # 2 in my answer . - romeo
  • I tried - the result is the same ... I get the field - and the name is not - Jony

2 answers 2

In fact, what you described above is basically a working version. Yes, there will be 1000, and you will work with this thousand. But one shouldnā€™t forget about page-by-page navigation (and the fact that itā€™s farther than the 10th one comes just for the look through ā€™and can be excluded in principle), and one thousand queries donā€™t need to be done, everything can be done in one request:

 SELECT * FROM TableName WHERE id IN (4,9,6,2...)

However, in order to preserve relevance, it is necessary that this order of ID students is derived in the same order that it was given by sphix. To do this, a little sql-magic:

 SELECT * FROM TableName WHERE id IN (4,9,6,2...) ORDER BY FIELD (id, 4,9,6,2...)

But during my work with sphinx, I acquired my own (or maybe not) point of view of working with it.

Sphinx allows you to store data for output (aggregation of data), both in the fields that the search is being done and in those fields that the search is not being done. Therefore, you can configure sphinx.conf so that it, along with id, gives out other fields - title, category_name, tags_name, etc. etc. The search result page does not require connection to the database in principle. And we have the opportunity to make cool "autocomplete".

  • Thanks for the detailed and high-quality answer! Could you give an example of one of the described sources in the sphinx.conf file? To understand how to customize the output fields? Now I have an output array from the search that does not contain anything to indicate from which table the obtained ID's, I must at least make the output field type fixed by the values ā€‹ā€‹for each query. - Jony
  • I noticed that the search array sql_field_string fields that are described in the config as: sql_field_string , while there are sql_attr_uint = type that do not fall into this array. - Jony
  • gist.github.com/VasyOk/9df7614fc6d101d40c2b - a terribly old config that I wrote for a site that is no longer working, I thought I deleted it. - VasyOk
  • @VasyOk> for those fields for which the search is not done It is better not to get involved, because the whole thing lies in the memory. Proper practice will store fields other than string with an additional query in the database, as you, in fact, noted above. An exception may be a scenario in which you need to display 1-2 fields with a limited number of characters (VARCHAR), for example, title . @Oleg Ponomarchuk I suggest to look at my config for the current version of Sphinx 2.2.6 github.com/romeOz/rock/blob/master/tests/data/sphinx/ā€¦ - romeo
  • @Oleg Ponomarchuk There are times when using sql_attr_multi (MVA) helps a lot. For example, if you look at the main hashcode.ru , you will notice that in each snippet of the question there is a set of tags. In the case of RDBMS , this is typically an M: M connection ("many-to-many"). Sphinx will cope with this task faster than standard DBMS tools (JOINs or several queries). Here's a good little article about MVA. - romeo

For example, we need to create an index for articles ( articles_index ):

  • An example config .
  • We kill the demon that started after installing Sphinx: sudo service sphinxsearch stop
  • Create an index: sudo indexer --config /path/to/sphinx.conf --all

Errors should not be. Unless: WARNING: Attribute count is 0: switching to none docinfo and WARNING on common_source - do not pay attention.

  • Run the daemon: sudo searchd --config /path/to/sphinx/sphinx.conf
  • Connecting: mysql -h0 -P9306

    -h0 is localhost. -P9306 is the port on which the Sphinx daemon weighs.

  • SphinxQL syntax query: SELECT * FROM articles_index WHERE MATCH('тŠµŃŃ‚'); We get an array of id-cov.
  • Actually received id-s inserted into the query SELECT * FROM articles WHERE id IN (4,9,6,2...) , if necessary with the observance of the order, then: SELECT * FROM articles WHERE id IN (4,9,6,2...) ORDER BY FIELD (id, 4,9,6,2...)

As you can see, we make two requests: to the Sphinx daemon ( articles_index index), and then to the DBMS ( articles table).

and how do I determine from which table the ID's are obtained.

I think I answered your question.

And can this query be done with Sphinx?

Out of the box, Sphinx provides APIs for various languages, including PHP. As a rule, all APIs are stored /usr/share/sphinxsearch/api/ . Copy sphinxapi.php to your project and connect. An example of using the API . Be careful, the article for 2010. Current API information . I personally use the extension for Yii2 , or rather, I introduced this extension into my Rock Framework with some changes.

If you had in mind if Sphinx, having received the ID's, can independently make a request to the DBMS - no. Its task is to index (it makes a batch request to the DBMS and adds everything to the index on the disk, and some of it is stored in the memory - the hot index data. About RT-indices with full-fledged CRUD is another story) and search with issuing ID's. In some cases, you may need to display other attributes (wrote in the comments).

Update # 1

Result in the console:

alt text

Update # 2

Here is the result of using sphinxapi.php (PECL analog). Config sphinx.conf . As you can see, the search result is placed in matches .

Some notes:

  • If matches missing, then nothing is found for your search.

Instead of $_POST['search'] specify a search term with "pens".

  • After any changes to sphinx.conf , reindexing is required: sudo indexer --config /path/to/sphinx.conf --all --rotate .
  • When using the API (if more specifically, the SphinxClient::setMatchMode() method), I noticed an error of the form Deprecated: DEPRECATED: Do not call this method or, even better, use SphinxQL instead of an API . How would the authors in the person of Aksonov and Co hint that using the API at the current time is not a good practice. Use SphinxQL through ordinary PDO, namely: http://pastebin.com/UABtVzys
  • In your example config, one source is compiled, but without specifying the fields. It is also not clear how to determine in the output array from which table the ID's are obtained - Jony
  • > from which table are obtained the `` `... sql_query = SELECT id, title, 'articles' AS table FROM articles WHERE id> = $ start AND id <= $ end sql_field_string = table ...` `Like I had to use exclusively for UNION requests. - romeo
  • I will give an example of my resource in the question, I still do not understand this moment - Jony
  • My table is defined as: sql_attr_uint = type and for some reason is not included in the result - Jony
  • Romeo, may I ask you to share your knowledge about Sphinx, namely the types of sql_attr_uint attributes and other basics? - Jony