Some issues with Sphinx and PHP

Here is the case:

I have a big table in my database – 3.6 GB and 1,7M rows. Selecting with limit and offset from the table is extremely slow and often results in Error 504. Table is MyISAM, has multiple indexes and will be updated.

That’s why I decided to use Sphinx to list the contents of the table – in some cases without query (all the rows), in some cases with query – and it works like a charm. The speed is amazing.

But here’s the problem – without a query only the first 1,000 results are returned. Even if I call it with $this->SetLimits(41, 24); which should return me the results with id from 984 to 1008, but the last result I get is id 1,000. I tried changing the value in my /etc/sphinxsearch/sphinx.conf, restarted the service, but no success.
Also, is that a good idea? I mean, will it slow down the performance noticeably? Any suggestions on how to do this?

And here is another question:

In my sphinx.conf I selected the columns I want to use for search/order and they all appear in the [matches] section. Since I don’t use them, they only take memory and (I guess) reduce the performance to some extend. I only use id in my application. Is it possible to search/order by these columns, but exclude them from [matches]?

My sphinx.conf

source test {

  type          = mysql

  sql_host      = localhost
  sql_user      = root
  sql_pass      = password
  sql_db        = database
  sql_port      = 3306

  sql_query     = 
  SELECT id, name,  UNIX_TIMESTAMP(date) as date, views, rating 
  FROM table 
  WHERE active = 1

  sql_field_string      = name
  sql_attr_timestamp    = date
  sql_attr_uint         = views
  sql_attr_uint         = rating

  sql_query_info        = SELECT * FROM table WHERE id=$id


index test {

  source            = test
  path              = /var/lib/sphinxsearch/data/test
  docinfo           = extern
  charset_type      = utf-8


searchd {

  listen            = 9312
  log               = /var/log/sphinxsearch/searchd.log
  query_log         = /var/log/sphinxsearch/query.log
  read_timeout      = 5
  max_children      = 30
  pid_file          = /var/run/sphinxsearch/
  max_matches       = 10000 # I tried changing this to 10,000 - no result
  seamless_rotate   = 1
  preopen_indexes   = 1
  unlink_old        = 1
  binlog_path       = /var/lib/sphinxsearch/data


Source: mysql

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.