Email me mysql/sphinx query result

Hi. Here's just a simple trick which can be useful if you run some long-lasting mysql/sphinx query in a screen or whatever and want to get informed as soon as it's finished. This works on mysql client level and applicable to MySQL and SphinxQL:

The trick is to use "pager" directive to redirect the output to a mail program:

mysql> pager mail -s "subject" yourname@yourdomain.com
PAGER set to 'mail -s "subject" yourname@yourdomain.com'

The above will redirect the output to yourname@yourdomain.com and the subject will be "subject".

Then just start your long-lasting query to MySQL:

mysql> select count(*) from feed where ext_key like 'a%';
1 row in set (10 min 35.88 sec)

or SphinxQL:

mysql> select * from huge_index;
20 rows in set (1 min 14.00 sec)

and be informed via email.

---

Need help with search infrastructure? Talk to our engineering team — we've built and maintained search systems for over 15 years.

Frequently Asked Questions

How do you redirect MySQL query output to email?

Use the MySQL client pager directive to pipe output to the mail command: pager mail -s "subject" email@domain.com. This redirects all subsequent query results to the specified email address. When a long-running query completes, the results are emailed automatically. Reset to normal terminal output with the nopager command.

Does the pager email trick work with SphinxQL?

Yes. Since SphinxQL uses the MySQL protocol on port 9306, the same pager directive works identically. Connect to Sphinx via the MySQL client (mysql -P9306 -h127.0.0.1), set the pager to mail, and run your query. This is especially useful for large index scans or complex full-text queries that take minutes to complete.

What alternatives exist for monitoring long-running MySQL queries?

Beyond the pager email trick, you can use pt-query-digest from Percona Toolkit to log slow queries, set up PMM Query Analytics for continuous monitoring, or write a wrapper script that runs queries via mysql -e and sends results through sendmail. For production environments, prefer PMM over ad-hoc notification solutions.

Related Reading

• Correct ordering of search result set using Sphinx
• Announcing DokuWiki Sphinx Search Plugin
• High performance BuildExcerpts() with Sphinx Search