What is the most efficient way to get full row data from MySQL when using Sphinx?

Question

I am trying to use MySQL with Sphinx to provide fulltext searching on usernames in order to provide a user search/autocomplete feature.

I have everything set up using the sphinx php api and have managed to get a list of userid's returned with this code:

include('/usr/local/lib/php/sphinxapi.php');

  $sphinx = new \SphinxClient();
  $mysqlconn = mysql_connect("127.0.0.1:9306") or die ("Couldn't connect to MySQL.");
  $results = $sphinx->Query("John", "users");

  $ids = "";
        foreach($results['matches'] as $id => $data) {
             if($ids > ""){
                     $ids .= ",";
             }
            $ids .= $id;
        }


    echo json_encode($ids);

This returns a comma seperated list of userid's which have the word 'John' in eitheer their firstname or lastname column

Taking this approach I would now need to query the actual mysql database and pull out the users matching those ID's.

My question is what would be the most efficient way of doing this? Should I use a simple select with an IN() clause or is there a far better way?

Also can Sphinx not return this data for me itself automatically in a more efficient manner?

Thanks in advance

Ray Ray · Accepted Answer · 2012-10-11T14:01:10

Yup, querying again the mysql database separately is required when using sphinx... You looking up a key in one db and then using it in another DB. No way around it--sort of like using the yellow pages to find a phone number, you still have to dial the number on your phone to speak to the person. There is no way to construct a join across the two DB's (outside of writing a wrapper library to make it look like it's doing so).

However....

An option you have is to convert your DB over to MariaDB (a drop in replacement for MySQL). As of 5.2.2 they have integration with sphinx as a storage engine: https://kb.askmonty.org/en/about-sphinxse/2941/

What is the most efficient way to get full row data from MySQL when using Sphinx?

2 Answers