Alexander Rubin's Blog on MySQL

Reporting queries (I will use this term here) are the queries which summaries and groups data over the certain period of time. For example, in Social Network site we want to know how many messages have been sent for the given period of time, group by region and status (sent, received, etc), order by number of messages sent.

As an example I will take a table which is used to send SMS (text messages).

SQL: select concat('+', substring(region_code,1 ,2), 'xxx') as reg, status, count(*) as cnt
from messages
where submition_date between '2009-01-01' and '2009-04-01' group by reg, status
having cnt>100 order by cnt desc, status limit 100;

This query will do a range scan over the submition_date and perform a filesort. There are common well known approaches which can be used to optimize table (“covered index”, “summary tables”, using external data warehouse, etc). Sometimes those approaches do not work or too complex.

Yet another approach is to use external search/index solution, for example Sphinx Search (http://www.sphinxsearch.com). In this case, data will be stored in MySQL and sphinx will be used as an external indexer/searcher, with SQL protocol support.

Using Sphinx

Starting with version 0.9.9-rc2, Sphinx searchd daemon supports MySQL binary network protocol and can be accessed with regular MySQL API. For instance, ‘mysql’ CLI client program works well. Here’s an example of querying Sphinx using MySQL client:

$ mysql -P 3307
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 0.9.9-dev (r1734)

As Sphinx can use attributes (“fields”) and group/sort then, it can be used for our report. Also, an application can simply connect to Sphinx server with MySQL protocol: an application will think it will work with MySQL (there are minor differences in Sphinx SQL, like “@count” and support of timestamps only instead of datetime)

Here is the example of the above query in Sphinx:

Same query in MySQL 5.1 runs much slower:

2 import notes:

1. For now, Sphinx can’t group by more than one field. However, we can combine 2 fields in 1 and then group by this new field. Here the example of how we can do it:
2. In the configuration file (in searchd section) we need to set max_matches to very large number (max_matches = 10000000 for example). By default, Sphinx will not generate exact counts (and all other average functions); this was done for the purpose of speed. However, setting max_matches to large number fixes this issue.

More speed comparison, group by 2 fields:

Sphinx:

MySQL:

Conclusion

If you need fast ad-hock reporting queries, SphinxSearch can be a good option.
Advantages:

• Faster sorting and grouping (which is very important for reporting queries)
• No need to use external API for queries, Sphinx now supports mysql protocol

Disadvantages:

• Need to run additional Sphinx daemon
• Need to re-index data when it is changing

Sphinx config file

source src1
{
type                                    = mysql
sql_host                                = 127.0.0.1
sql_user                                = root
sql_pass                                =
sql_db                                  = dw
sql_port                                = 3309  # optional, default is 3306
sql_query                               = \
SELECT msg_id, region_code, status+0 as status_code,  UNIX_TIMESTAMP(submition_date) AS date_added, 't' as  content \
FROM messages_dw
sql_attr_uint                   = region_code
sql_attr_uint                   = status_code
sql_attr_timestamp              = date_added
sql_query_info                  = SELECT * FROM messages_dw WHERE msg_id=$id
}
index messages_dw
{
source                                  = src1
path                                    = /data1/arubin/sphinx_new//var/data/test1
docinfo                                 = extern
charset_type                    = sbcs
}
indexer
{
mem_limit                               = 32M
}
searchd
{
listen = localhost:3312:mysql41
log                                             = /data1/arubin/sphinx_new//var/log/searchd.log
query_log                               = /data1/arubin/sphinx_new//var/log/query.log
read_timeout                    = 30
max_children                    = 30
pid_file                                = /data1/arubin/sphinx_new//var/log/searchd.pid
max_matches                             = 10000000
seamless_rotate                 = 1
preopen_indexes                 = 0
unlink_old                              = 1
}