problem compiling Mysql 5.1 with sphinx engine support

Mysql's full-text search engine is not very powerful and support comes only with Myisam engine. Sphinx is a full-text search engine replacement for Mysql and Postgresql. Also, It's possible to use Sphinx full-text search engine with both Myisam and InnoDB engines. You can read detailed information about mysql compilation with sphinx on Sphinx site.

I have faced with following error message, while compiling Mysql with innodb and sphinx engine support ( ./configure --prefix=/data/mysql3/ --with-plugins=sphinx,innobase). I used the innodb (base) which bundled with Mysql. You can also compile innodb plugin which has additional features. For example, on the fly compression, speed improvements.

configure: error: unknown plugin: sphinx

The trick was to run autorun.sh command in Mysql's source tree before running configure with sphinx engine support. This script will create a new configure script which knows how to compile sphinx engine.

# ./BUILD/autorun.sh

Mysql General Query Log

Before Mysql version 5.1.6, you can only log to files what mysqld was doing. 5.1.6 comes with an option where it let's you keep this log in a table called mysql.general_log. Even further, version 5.1.12 came with a new ability to let you disable general query logging on the fly.

You can enable/disable mysql general query logging by setting global variable "general_log".For example following command, starts general logging on the fly.

SET GLOBAL general_log = 'ON';

You have to options, either you can log the queries on in a file where it was the only option before version 5.1.6 , or you can log in the table I mentioned above called mysql.general_log by using global variable "log_output".
The following statement tells mysql to log queries in table mysql.general_log instead of a file.

set global log_output='table';

In any time, you can truncate the table to delete it's contents.




After logging the queries, you can see them by simply using a select statement.

select * from mysql.general_log;

to make it more readable you can use \G at the end of the query.
select * from mysql.general_log\G;

profiling mysql queries

Mysql database server has a new query profiling option. It's a very useful feature to profile queries on server and find bottlenecks.
There is an article on mysql site which describes this feature in detail.
Using the New MySQL Query Profiler