CODEWITHSUNDEEP
sphinx
J. Doe
J. Doe

Reputation: 29

How to search numeric by Sphinx correctly?

I need make search on billion records in MySQL and it's very long process (it's works now). May be Sphinx help me? How correctly to configure Sphinx for search numbers? Should I use integer attribute for searching (not string field)?

I need to get only row where the timestamp 'nearest or equal' to query:

CREATE TABLE test ( date TIMESTAMP(6) UNIQUE, num INT(32) );
| 2018-07-02 05:50:33.084011 |  282 |
| 2018-07-02 05:50:33.084028 |  475 |
...

(40 M such rows... all timestamps is unique, so this column are unique index so I no need in create additional index I suppose.)

sphinx.conf:

source src1
{
type = mysql
...        
sql_query = SELECT * FROM test
}

indexer...

Sphinx 3.0.3
...
indexing index 'test'...
collected 40000000 docs, 0.0 MB

In my test I find nearest timestamp to query:

$start = microtime(true);
$query = '2018-07-02 05:50:33.084011';
$connMySQL = new PDO('mysql:host=localhost;dbname=test','','');
$sql = "SELECT * FROM test WHERE date <= '$search' ORDER BY date DESC LIMIT 1";
$que  = $connMySQL->query($sql);
$result = $que->fetchAll(PDO::FETCH_ASSOC);
$query  = $connMySQL->query('reset query cache');
$connMySQL = null;
print_r ($result);
echo 'Time MySQL:'.(microtime(true) - $start).' sec.';

$start = microtime(true);
$query = '2018-07-02 05:50:33.084029';
$connSphinxQL = new PDO('mysql:host=localhost;port=9306;dbname=test','root','');
$sql = "SELECT * FROM test WHERE date <= '$search' ORDER BY date DESC LIMIT 1";
$que  = $connSphinxQL->query($sql);
$result = $que->fetchAll(PDO::FETCH_ASSOC);
$query  = $connSphinxQL->query('reset query cache');
$connSphinxQL = null;
print_r ($result);
echo 'Time Sphinx:'.(microtime(true) - $start).' sec.';

Output:

[date] => 2018-07-02 05:50:33.084011 [num] => 282 Time MySQL: 0.00193 sec.
[date] => 2018-07-02 05:50:33.084028 [num] => 475 Time Sphinx: 0.00184 sec.

I suggested to see some different resuts, but noticed that before indexing I have got the same result, so I think Sphinx searches directy in MySQL by the reason of my wrong configuration. Only ask here I found: no text search

Upvotes: 0

Views: 502

Answers (1)

barryhunter
barryhunter

Reputation: 21091

Should I use integer attribute for searching (not string field)?

Yes. But an added complication, is a index NEEDS at least one field (sphinx isnt really designed as a general database, its intended for text queries!)

Can synthesize a fake one. 

sql_query = SELECT unix_timestamp(`date`) AS id, 'a' AS field, num FROM test
sql_attr_uint = num

Also shows that need a unique integer as the first column, to be a document_id, seems as your timestamp is unique, can use that. a UNIX_TIMESTAMP is a nice easy way to represent a timestamp as a plain integer.

Can use id in queries too, for filtering, so would need to convert to a timestamp at the same time. 

$query = '2018-07-02 05:50:33.084011';
$id = strtotime($query)
$sql = "SELECT * FROM test WHERE id <= '$id' ORDER BY id DESC LIMIT 1";

Upvotes: 0

Related Questions