Some issues with Sphinx and PHP

Question

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/searchd.pid
  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

}

Answer 1

This is because there's per-query max-matches setting as well, and it defaults to 1000.

in sphinx.conf you could set something like

max_matches = 10000 

or, add max_matches to your query

$this->SetLimits(41, 24, 10000);

Note that there are two places where max_matches limit is enforced. Per-query limit is controlled by this API call, but there also is per-server limit controlled by max_matches setting in the config file. To prevent RAM usage abuse, server will not allow to set per-query limit higher than the per-server limit.

Answer 2

For the second part look at the ->SetSelect() function.