MySQL vs. PostgreSQL

Which is better, PostgreSQL or MySQL? Both are excellent products with unique strengths, and the choice is often a matter of personal preference. Read on for a useful comparison of these two open source database systems.

Actually many people asked, “Which is the best PostgreSQL or MySQL?” The answer is always the same: “It’s a matter of preference.” You could ask many developers the same question, and their responses will all be different. Here is a comparison of MySQL and PostgreSQL databases, offered not for the sake of voicing my opinion, but to help you make your own decision.

Both systems have much to offer in terms of stability, flexibility, and performance. MySQL has features that PostgreSQL lacks, and vice versa. However, my primary focus is to help you determine which of the two databases to use in your own development.

MySQL has many different table types that support transactions and foreign keys and are compliant with ACID. However, some of the configurations of these table types are quite complex. Not many Web developers or programmers use the additional table types found in MySQL. With that said, let’s compare these two products.

When to use MySQL

Why would you use MySQL over PostgreSQL? First, we need to consider the needs of the applications in terms of database requirements. If I want to create a Web application and performance is an issue, MySQL will be my choice because it’s fast and designed to work well with Web-based servers. However, if I want to create another application that demands transactions and foreign key references, PostgreSQL is the choice. (No doubt, with InnoDB storage engine we can use the transactions as well as foreign key in MySQL 5.0)

I explained here difference as per my knowledge.

Some reasons for using MySQL over PostgreSQL:

• MySQL is relatively faster than PostgreSQL.
• Database design will be simpler.
• You can create a basic Web-driven Web site.
• MySQL’s replication has been thoroughly tested.
• There’s no need for cleanups in MySQL (Vacuum).

Some reasons for using PostgreSQL over MySQL:

• Complex database design
• Moving away from Oracle, Sybase, or MSSQL
• Complex rule sets (i.e., business rules)
• Use of procedural languages on the server
• Transactions
• Use of geographical data
• R-Trees (i.e., used on indexes)

Comparison

The comparsion of the newest, stable and production version of PostgreSQL 8.0 and MySQL 4.1 (MySQL has also development version 5.0).

	PostgreSQL 8.0	MySQL 5.0
Operating System	Windows, more than 2 dozen Unix-like operating systems (Linux, all BSDs, HP-UX, AIX, OS X, Unixware, Netware…)	Linux, Windows, FreeBSD, MacOS X, Solaris, HP UX, AIX, and many others.
ANSI SQL compliance	ANSI-SQL 92/99	Possible; user can run MySQL in more ANSI compatible (ANSI mode)
Performance	Slower	Faster
Sub-selects	Yes	Yes
Transactions	Yes	Yes
Database replication	Yes	Yes
Foreign key support	Yes	Yes
Views	Yes	Yes
Stored procedures	Yes (pl/SQL)	Yes (procedural)
Triggers	Yes	Yes
Unions	Yes	Yes
Full joins	Yes	No planned for 5.1
Constraints	Yes	No Planned for 5.1
Cursors	Yes	Partial (read only)
Procedural languages (PLs)	Yes PL/pgSQL, PL/Tcl, PL/Perl, PL/Python PL/PHP, PL/Java or user defined	Yes Supports stored procedures (persistent modules) languages as defined by ANSI SQL 2003
Vacuum (cleanup)	Yes	Yes by OPTIMIZE TABLE
Different table types	No (PostgreSQL has its own inbuilt table types and doesn’t use any alternative ones)	Yes MyISAM, InnoDB, MEMORY, BerkeleyDB, MERGE, Archive and NDB (Cluster), Federated (InnoDB has additional functionality)
ODBC	Yes	Yes
JDBC	Yes	Yes
Other APIs	Most of languages (i.e. Perl, C/C++, .NET, OLE-DB, Tcl/Tk, Python, PHP, …)	Most of languages
IPv6 support	Yes	No Planning in 5.1
WWW	- Homepage, - Manual, - Features	- Homepage, - Manual, - Features, - What’s new in 5.0,
Installation	PostgreSQL installation	MySQL installation
Download	Download page	Download page

MySQL Crash Recovery

MySQL Crash Recovery
MySQL is known for its stability but as any other application it has bugs so it may crash sometime. Also operation system may be flawed, hardware has problems or simply power can go down which all mean similar things – MySQL Shutdown is unexpected and there could be various inconsistences. And this is not only problem as we’ll see.
MySQL has angel process mysqld_safe which will restart MySQL Server in most cases. It is great, unless you have run into some bug which causes it to crash again – such crashes qucikly following one another are kind of worse because they explore many less tested code paths in MySQL and so problem potential is larger.
So lets look at the problem which happen during the crash which might need to take care of or which may seriously affect MySQL Performance.
MyISAM Corruption - If you’re writing to MyISAM tables there is very large chance of them becoming corrupted during the crash. Note corruption may be hidden and do not expose itself instantly – you may notice wrong query results days after crash. Sometimes corrupted tables may be reason for further crashes or hangs, and corruption may spread itself further in the table. You probably do not want any of these so it is very good idea to run MySQL with myisam_recover option which will make sure all improperly closed MyISAM tables are checked first time it is accessed. This option is however rather painful to use with web applications – users may issue different queries which may trigger check/repair running for many tables at onces, which typically make system extremely slow and also can use up all allowed connections or run out of memory ( myisam_sort_buffer_size is normally set pretty lage). If this becomes the problem I use tiny script which moves out all MyISAM tables out of MySQL database directory, checks them with MyISAMchk and moves them back to running server. This looks scary but it works great – until table is checked and ready application gets error rather than stalling forever which allows application to become partially functional as soon as possible. This hack is needed only in some cases – in most cases using Innodb for tables which you need to be recovered fast is better solution.
Innodb Recovery – Unless you have some hardware problems (99%) or found new Innodb bug (1%) Innodb recovery should be automatic and bring your database to consistent state. Depending on innodb_flush_lot_at_trx_commit setting you may lose few last committed transactions but it is it. It is Performance of this process which may cause the problems. As I already wrote innodb_log_file_size and innodb_buffer_pool_size affect recovery time significantly as well as your workload. I should also mention if you have innodb_file_per_table=1 your recovery speed will depend on number of Innodb tables you have, as well as many other operations, so beware.
Binary log corruption - Binary log may become corrupted and out of sync with database content. This will sometimes break replication but if you’re just planning on using binary log for point in time recovery it can go unnoticed. sync_binlog Is helping by syncing binary log, but at performance penalty. If using Innodb you also might with to use innodb-safe-binlog option in MySQL 4.1 so your Innodb log and binary log are synchronized. In MySQL 5.0 XA is taking care of this synchronization.
.frm Corruption – Few people know MySQL is not really ACID even with Innodb tables, at least not for DDL statements.
There is a chance of failing for example during CREATE statement with table created in Innodb dictionary but .frm not created or not completely written. Partially written .frm files or .frm being unsync with internal Innodb dictionary may cause MySQL to fail with wierd error messages. In MySQL 4.1 sync_frm option was added which reduces this problem as time window when it can happen is much less. Still if failure happens just during writting .frm file nasty things may happen, not to mention such potentially multiple operation DDL statements as RENAME TABLE – these are most vulnerable.
master.info corruption - If slave happens to crash you can also have relay logs corruption and master.info being corrupted. Not to mention MyISAM tables can contain partially completed statements as well as some of updates totally lost. The safe approach it to reclone the slaves if they crash or you can take the risks and try to continue. Sometimes you might be able to manually find appropriate position even if master.info file is out of sync but I would not be basing my failure handling scenarios.
Cold Start – If you restart MySQL server its caches (key_buffer, innodb_buffer_pool, query_cache,table_cache) are cleaned, so may be OS caches. This may reduce performance dramatically. So if you’re bringing server back after crash you might want to populate caches. For MyISAM key_cache this can be done by using LOAD INDEX INTO CACHE statement, for other storage engines it can be done by issuing large index scan queries. Full table scan queries allow to preload table data ether in storage engine caches or in OS cache. You can save these into .sql file and use –init-file to make sure it is run on startup. The other approach is to prime server with real servers (ie clone queries from other slave) before putting traffic to it.
In case application is not highly available so there is only one server you might with to start serving only some users initially (returning error to others) and gradually increase the load as server warms up. This may sound strange but makes a lot of sense as not only waiting for pages which never load is more frustrating for users than getting honest “try again later” message, but also – warmup takes longer time on extreme load.
Innodb statistics - Unlike MyISAM Innodb does not store index cardinality in tables, instead it computes them on first table access after startup. This may take significant time if you have very large number of tables (Some users have hundreds of thousands of tables per database host). This one is pretty much part of cold start problems but I wanted to point out it separately. To warmup this data you might run select 1 from _table_ limit 1 for each table or any other statement – it is table open which is important.
There are other problems which you may experience related to MySQL Crash Recovery – Restoring data from backup, corrupted Innodb tablespace recovery etc but I should write about them some other time.

Reference by : http://www.mysqlperformanceblog.com/2006/07/30/mysql-crash-recovery/

How MySQL Uses Indexes

How MySQL Uses Indexes

Types of Indexes

There are several types of indexes to choose from in MySQL:

1. “Normal” Indexes – “Normal” indexes are the most basic indexes, and have no restraints such as uniqueness. These can be added by creating an index (CREATE INDEX name_of_index ON tablename (columns_to_index);), altering the table (ALTER TABLE tablename ADD INDEX [name_of_index] (columns_to_index);), or when creating the table (CREATE TABLE tablename ( [...], INDEX [name_of_index] (columns_to_index) );).
2. Unique Indexes – Unique indexes are the same as “Normal” indexes with one difference: all values of the indexed column(s) must only occur once. These can be added by creating an index (CREATE UNIQUE INDEX name_of_index ON tablename (columns_to_index);), altering the table (ALTER TABLE tablename ADD UNIQUE [name_of_index] (columns_to_index);) or when creating the table (CREATE TABLE tablename ( [...], UNIQUE [name_of_index] (columns_to_index) );).
3. Primary keys – Primary keys are unique indexes that must be named “PRIMARY”. If you have used AUTO_INCREMENT columns, you’re probably familiar with these. These indexes are almost always added when creating the table (CREATE TABLE tablename ( [...], PRIMARY KEY (columns_to_index) );), but may also be added by altering the table (ALTER TABLE tablename ADD PRIMARY KEY (columns_to_index);). Note that you may only have one primary key per table.
4. Full-text indexes – Full-text indexes are used by MySQL in full-text searches. Because full-text search is so new and would add unnecessary complexity to this article, I won’t explain it here. Should you want more information?

Indexes are used to find rows with specific column values fast. Without an index MySQL has to start with the first record and then read through the whole table to find the relevant rows. The bigger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly get a position to seek to in the middle of the data file without having to look at all the data. If a table has 1000 rows, this is at least 100 times faster than reading sequentially. Note that if you need to access almost all 1000 rows, it is faster to read sequentially, because that minimizes disk seeks.

All MySQL indexes (PRIMARY KEY, UNIQUE, and INDEX) are stored in B-trees. Strings are automatically prefix- and end-space compressed.

Indexes are used in the following ways:

• To quickly find the rows that match a WHERE clause.
• To retrieve rows from other tables when performing joins.
• To find the MAX() or MIN() value for a specific indexed column. This is optimised by a preprocessor that checks if you are using WHERE key_part_# = constant on all key parts < N. In this case MySQL will do a single key lookup and replace the MIN() expression with a constant. If all expressions are replaced with constants, the query will return at once:

·                SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10

• To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for example, ORDER BY key_part_1,key_part_2 ). The key is read in reverse order if all key parts are followed by DESC.
• In some cases a query can be optimised to retrieve values without consulting the datafile. If all used columns for some table are numeric and form a leftmost prefix for some key, the values may be retrieved from the index tree for greater speed:

·                SELECT key_part3 FROM table_name WHERE key_part1=1

Suppose you issue the following SELECT statement:

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer tries to find the most restrictive index by deciding which index will find fewer rows and using that index to fetch the rows.

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimiser to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

MySQL can’t use a partial index if the columns don’t form a leftmost prefix of the index. Suppose you have the SELECT statements shown here:

mysql> SELECT * FROM tbl_name WHERE col1=val1;

mysql> SELECT * FROM tbl_name WHERE col2=val2;

mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

If an index exists on (col1, col2, col3), only the first of the preceding queries uses the index. The second and third queries do involve indexed columns, but (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).

MySQL also uses indexes for LIKE comparisons if the argument to LIKE is a constant string that doesn’t start with a wildcard character. For example, the following SELECT statements use indexes:

mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%";

mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";

In the first statement, only rows with "Patrick" <= key_col < "Patricl" are considered. In the second statement, only rows with "Pat" <= key_col < "Pau" are considered.

The following SELECT statements will not use indexes:

mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%";

mysql> SELECT * FROM tbl_name WHERE key_col LIKE other_col;

In the first statement, the LIKE value begins with a wildcard character. In the second statement, the LIKE value is not a constant.

MySQL 4.0 does another optimization on LIKE. If you use ... LIKE "%string%" and string is longer than 3 characters, MySQL will use the Turbo Boyer-Moore algorithm to initialise the pattern for the string and then use this pattern to perform the search quicker.

Searching using column_name IS NULL will use indexes if column_name is an index.

MySQL normally uses the index that finds the smallest number of rows. An index is used for columns that you compare with the following operators: =, >, >=, <, <=, BETWEEN, or a LIKE with a pattern that begins with a non-wildcard prefix like 'something%'.

Any index that doesn’t span all AND levels in the WHERE clause is not used to optimise the query. In other words: To be able to use an index, a prefix of the index must be used in every AND group.

The following WHERE clauses use indexes:

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */

... WHERE index_part1='hello' AND index_part_3=5

          /* optimised like "index_part1='hello'" */

... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

          /* Can use index on index1 but not on index2 or index 3 */

These WHERE clauses do not use indexes:

... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */

... WHERE index=1 OR A=10                  /* Index is not used in

                                                        both AND parts */

... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows  */

Note that sometime MySQL will not use an index, even if one is available. One instance of this is when use of the index would require MySQL to access more than 30% of the rows in the table. (In this case a table scan is probably much faster, as it will require many fewer seeks.) However, if such a query uses LIMIT to only retrieve part of the rows, MySQL will use an index anyway, as it can much more quickly find the few rows to return in the result.

Analyzing Index Efficiency

You have some ideas on which indexes to use, but you’re not sure which is the most efficient. Well, you’re in luck, because MySQL has a built-in SQL statement to do this, known as EXPLAIN. The general syntax for this is EXPLAIN select statement;Here’s an example:

EXPLAIN SELECT peopleid FROM people WHERE firstname='Mike'
AND lastname='Sullivan' AND age='17';

This will return a somewhat cryptic result that will look usually look similar to this:

[Note: table split across two rows for readability]

+--------+------+-----------------+-----------------+
| table  | type | possible_keys   | key             |
+--------+------+-----------------+-----------------+  ...
| people | ref  | fname_lname_age | fname_lname_age |
+--------+------+-----------------+-----------------+

+---------+-------------------+------+------------+
| key_len | ref               | rows | Extra      |
... +---------+-------------------+------+------------+
| 102     | const,const,const | 1    | Where used |
+---------+-------------------+------+------------+

Let’s break this down column by column.

• table – This is the name of the table. This will become important when you have large joins, as each table will get a row.
• type – The type of the join. Here’s what the MySQL documentation has to say about the ref type: All rows with matching index values will be read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key, or if the key is not UNIQUE or a PRIMARY KEY (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this join type is good. In this case, since our index isn’t UNIQUE, this is the best join type we can get. In summary, if the join type is listed as “ALL” and you aren’t trying to select most of the rows in the table, then MySQL is doing a full table scan which is usually very bad. You can fix this by adding more indexes. If you want more information, the MySQL manual covers this value with much more depth.
• possible_keys – The name of the indexes that could possibly be used. This is where nicknaming your index helps. If you leave the name field blank, the name defaults to the name of the first column in the index (in this case, it would be “firstname”), which isn’t very descriptive.
• key – This shows the name of the index that MySQL actually uses. If this is empty (or NULL), then MySQL isn’t using an index.
• key_len – The length, in bytes, of the parts of the index being used. In this case, it’s 102 because firstname takes 50 bytes, lastname takes 50, and age takes 2. If MySQL were only using the firstname part of the index, this would be 50.
• ref – This shows the name of the columns (or the word “const”) that MySQL will use to select the rows. Here, MySQL references three constants to find the rows.
• rows – The number of rows MySQL thinks it has to go through before knowing it has the correct rows. Obviously, one is the best you can get.
• Extra – There are many different options here, most of which will have an adverse effect on the query. In this case, MySQL is simply reminding us that it used the WHERE clause to limit the results.

Disadvantages of Indexing

So far, I’ve only discussed why indexes are great. However, they do have several disadvantages.

First, they take up disk space. Usually this isn’t significant, but if you decided to index every column in every possible combination, your index file would grow much more quickly than the data file. If you have a large table, the index file could reach your operating system’s maximum file size.

Second, they slow down the speed of writing queries, such as DELETE, UPDATE, and INSERT. This is because not only does MySQL have to write to the data file, it has to write everything to the index file as well. However, you may be able to write your queries in such a way that the performance degradation is not very noticeable.

Conclusion

Indexes are one of the keys to speed in large databases. No matter how simple your table, a 500,000-row table scan will never be fast. If you have a site with a 500,000-row table, you should really spend time analyzing possible indexes and possibly consider rewriting queries to optimize your application.

Query Reference

Adding a “normal” index via CREATE INDEX:
CREATE INDEX [index_name] ON tablename (index_columns);

Example: CREATE INDEX fname_lname_age ON people (firstname,lastname,age);

Adding a unique index via CREATE INDEX:
CREATE UNIQUE INDEX [index_name] ON tablename (index_columns);

Example: CREATE UNIQUE INDEX fname_lname_age ON people (firstname,lastname,age);

Adding a “normal” index via ALTER TABLE:
ALTER TABLE tablename ADD INDEX [index_name] (index_columns);

Example: ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);

Adding a unique index via ALTER TABLE:
ALTER TABLE tablename ADD UNIQUE [index_name] (index_columns);

Example: ALTER TABLE people ADD UNIQUE fname_lname_age (firstname,lastname,age);

Adding a primary key via ALTER TABLE:
ALTER TABLE tablename ADD PRIMARY KEY (index_columns);

Example: ALTER TABLE people ADD PRIMARY KEY (peopleid);

Adding a “normal” index via CREATE TABLE:
CREATE TABLE tablename (
rest of columns,
INDEX [index_name] (index_columns)
[other indexes]
);

Example:
CREATE TABLE people (
peopleid SMALLINT UNSIGNED NOT NULL,
firstname CHAR(50) NOT NULL,
lastname CHAR(50) NOT NULL,
age SMALLINT NOT NULL,
townid SMALLINT NOT NULL,
INDEX fname_lname_age (firstname,lastname,age)
);

Adding a unique index via CREATE TABLE:
CREATE TABLE tablename (
rest of columns,
UNIQUE [index_name] (index_columns)
[other indexes]
);

Example:
CREATE TABLE people (
peopleid SMALLINT UNSIGNED NOT NULL,
firstname CHAR(50) NOT NULL,
lastname CHAR(50) NOT NULL,
age SMALLINT NOT NULL,
townid SMALLINT NOT NULL,
UNIQUE fname_lname_age (firstname,lastname,age)
);

Adding a primary key via CREATE TABLE:
CREATE TABLE tablename (
rest of columns,
INDEX [index_name] (index_columns)
[other indexes]
);

Example:
CREATE TABLE people (
peopleid SMALLINT NOT NULL AUTO_INCREMENT,
firstname CHAR(50) NOT NULL,
lastname CHAR(50) NOT NULL,
age SMALLINT NOT NULL,
townid SMALLINT NOT NULL,
PRIMARY KEY (peopleid)
);

Dropping (removing) a “normal” or unique index via ALTER TABLE:
ALTER TABLE tablename DROP INDEX index_name; Example: ALTER TABLE people DROP INDEX fname_lname_age;

Dropping (removing) a primary key via ALTER TABLE:
ALTER TABLE tablename DROP PRIMARY KEY; Example: ALTER TABLE people DROP PRIMARY KEY;

When MySQL uses indexes

• Using >, >=, =, <, <=, IF NULL and BETWEEN on a key.
  • SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;
  • SELECT * FROM table_name WHERE key_part1 IS NULL;
• When you use a LIKE that doesn’t start with a wildcard.
  • SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'
• Retrieving rows from other tables when performing joins.
  • SELECT * from t1,t2 where t1.col=t2.key_part
• Find the MAX() or MIN() value for a specific index.
  • SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
• ORDER BY or GROUP BY on a prefix of a key.
  • SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3
• When all columns used in the query are part of one key.
  • SELECT key_part3 FROM table_name WHERE key_part1=1

When MySQL doesn’t use an index

• Indexes are NOT used if MySQL can calculate that it will probably be faster to scan the whole table. For example if key_part1 is evenly distributed between 1 and 100, it’s not good to use an index in the following query:
  • SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
• If you are using HEAP tables and you don’t search on all key parts with =
• When you use ORDER BY on a HEAP table
• If you are not using the first key part
  • SELECT * FROM table_name WHERE key_part2=1
• If you are using LIKE that starts with a wildcard
  • SELECT * FROM table_name WHERE key_part1 LIKE '%jani%'
• When you search on one index and do an ORDER BY on another
  • SELECT * from table_name WHERE key_part1 = # ORDER BY key2

Full–Text Index For MyISAM in MySQL

Full–Text Index For MyISAM

• A query expansion search is a modification of a natural language search. The search string is used to perform a natural language search. Then words from the most relevant rows returned by the search are added to the search string and the search is done again. The query returns the rows from the second search. The WITH QUERY EXPANSION modifier specifies a query expansion search.

• When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first. Relevance values are non-negative floating point numbers. Zero relevance means no similarity. Relevance is computed based on the number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word.

MySQL can perform boolean full-text searches using the IN BOOLEAN MODE modifier:

Boolean full-text searches have these characteristics:

They do not use the 50% threshold.

They do not automatically sort rows in order of decreasing relevance. You can see this from the preceding query result: The row with the highest relevance is the one that contains “MySQL” twice, but it is listed last, not first.

They can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow.

The minimum and maximum word length full-text parameters apply.

The stopword list applies.

The boolean full-text search capability supports the following operators:

+

A leading plus sign indicates that this word must be present in each row that is returned.

-

A leading minus sign indicates that this word must not be present in any of the rows that are returned.

Note: The – operator acts only to exclude rows that are otherwise matched by other search terms. Thus, a boolean-mode search that contains only terms preceded by – returns an empty result. It does not return “all rows except those containing any of the excluded terms.”

(no operator)

By default (when neither + nor – is specified) the word is optional, but the rows that contain it are rated higher. This mimics the behavior of MATCH() … AGAINST() without the IN BOOLEAN MODE modifier.

> <

These two operators are used to change a word’s contribution to the relevance value that is assigned to a row. The > operator increases the contribution and the < operator decreases it. See the example following this list.

( )

Parentheses group words into subexpressions. Parenthesized groups can be nested.

~

A leading tilde acts as a negation operator, causing the word’s contribution to the row’s relevance to be negative. This is useful for marking “noise” words. A row containing such a word is rated lower than others, but is not excluded altogether, as it would be with the – operator.

*

The asterisk serves as the truncation (or wildcard) operator. Unlike the other operators, it should be appended to the word to be affected. Words match if they begin with the word preceding the * operator.

“

A phrase that is enclosed within double quote (‘”’) characters matches only rows that contain the phrase literally, as it was typed. The full-text engine splits the phrase into words, performs a search in the FULLTEXT index for the words. Prior to MySQL 5.0.3, the engine then performed a substring search for the phrase in the records that were found, so the match must include non-word characters in the phrase. As of MySQL 5.0.3, non-word characters need not be matched exactly: Phrase searching requires only that matches contain exactly the same words as the phrase and in the same order. For example, “test phrase” matches “test, phrase” in MySQL 5.0.3, but not before.

If the phrase contains no words that are in the index, the result is empty. For example, if all words are either stopwords or shorter than the minimum length of indexed words, the result is empty.

The following examples demonstrate some search strings that use boolean full-text operators:

‘apple banana’

Find rows that contain at least one of the two words.

‘+apple +juice’

Find rows that contain both words.

‘+apple macintosh’

Find rows that contain the word “apple”, but rank rows higher if they also contain “macintosh”.

‘+apple -macintosh’

Find rows that contain the word “apple” but not “macintosh”.

‘+apple ~macintosh’

Find rows that contain the word “apple”, but if the row also contains the word “macintosh”, rate it lower than if row does not. This is “softer” than a search for ‘+apple -macintosh’, for which the presence of “macintosh” causes the row not to be returned at all.

‘+apple +(>turnover <strudel)’

Find rows that contain the words “apple” and “turnover”, or “apple” and “strudel” (in any order), but rank “apple turnover” higher than “apple strudel”.

‘apple*’

Find rows that contain words such as “apple”, “apples”, “applesauce”, or “applet”.

‘”some words”‘

Find rows that contain the exact phrase “some words” (for example, rows that contain “some words of wisdom” but not “some noise words”). Note that the ‘”’ characters that enclose the phrase are operator characters that delimit the phrase. They are not the quotes that enclose the search string itself.

Full-Text Searches with Query Expansion

Full-text search supports query expansion (and in particular, its variant “blind query expansion”). This is generally useful when a search phrase is too short, which often means that the user is relying on implied knowledge that the full-text search engine lacks. For example, a user searching for “database” may really mean that “MySQL”, “Oracle”, “DB2”, and “RDBMS” all are phrases that should match “databases” and should be returned, too. This is implied knowledge.

Blind query expansion (also known as automatic relevance feedback) is enabled by adding WITH QUERY EXPANSION following the search phrase. It works by performing the search twice, where the search phrase for the second search is the original search phrase concatenated with the few most highly relevant documents from the first search. Thus, if one of these documents contains the word “databases” and the word “MySQL”, the second search finds the documents that contain the word “MySQL” even if they do not contain the word “database”. The following example shows this difference:

mysql> SELECT * FROM articles

    -> WHERE MATCH (title,body) AGAINST ('database');

+----+-------------------+------------------------------------------+

| id | title             | body                                     |

+----+-------------------+------------------------------------------+

|  5 | MySQL vs. YourSQL | In the following database comparison ... |

|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |

+----+-------------------+------------------------------------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM articles

    -> WHERE MATCH (title,body)

    -> AGAINST ('database' WITH QUERY EXPANSION);

+----+-------------------+------------------------------------------+

| id | title             | body                                     |

+----+-------------------+------------------------------------------+

|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |

|  5 | MySQL vs. YourSQL | In the following database comparison ... |

|  3 | Optimizing MySQL  | In this tutorial we will show ...        |

+----+-------------------+------------------------------------------+

3 rows in set (0.00 sec)

Another example could be searching for books by Georges Simenon about Maigret, when a user is not sure how to spell “Maigret”. A search for “Megre and the reluctant witnesses” finds only “Maigret and the Reluctant Witnesses” without query expansion. A search with query expansion finds all books with the word “Maigret” on the second pass.

Note: Because blind query expansion tends to increase noise significantly by returning non-relevant documents, it is meaningful to use only when a search phrase is rather short.

Fine-Tuning MySQL Full-Text Search

MySQL’s full-text search capability has few user-tunable parameters. You can exert more control over full-text searching behavior if you have a MySQL source distribution because some changes require source code modifications.

Note that full-text search is carefully tuned for the most effectiveness. Modifying the default behavior in most cases can actually decrease effectiveness. Do not alter the MySQL sources unless you know what you are doing.

Most full-text variables described in this section must be set at server startup time. A server restart is required to change them; they cannot be modified while the server is running.

Some variable changes require that you rebuild the FULLTEXT indexes in your tables. Instructions for doing this are given at the end of this section.

· The minimum and maximum lengths of words to be indexed are defined by the ft_min_word_len and ft_max_word_len system variables. The default minimum value is four characters; the default maximum is version dependent. If you change either value, you must rebuild your FULLTEXT indexes. For example, if you want three-character words to be searchable, you can set the ft_min_word_len variable by putting the following lines in an option file:

·                [mysqld]

·                ft_min_word_len=3

Then you must restart the server and rebuild your FULLTEXT indexes. Note particularly the remarks regarding myisamchk in the instructions following this list.

· To override the default stopword list, set the ft_stopword_file system variable.The variable value should be the pathname of the file containing the stopword list, or the empty string to disable stopword filtering. After changing the value of this variable or the contents of the stopword file, restart the server and rebuild your FULLTEXT indexes.

The stopword list is free-form. That is, you may use any non-alphanumeric character such as newline, space, or comma to separate stopwords. Exceptions are the underscore character (‘_’) and a single apostrophe (‘'’) which are treated as part of a word. The character set of the stopword list is the server’s default character set.

· The 50% threshold for natural language searches is determined by the particular weighting scheme chosen. To disable it, look for the following line in myisam/ftdefs.h:

·                #define GWS_IN_USE GWS_PROB

Change that line to this:

#define GWS_IN_USE GWS_FREQ

Then recompile MySQL. There is no need to rebuild the indexes in this case. Note: By making this change, you severely decrease MySQL’s ability to provide adequate relevance values for the MATCH() function. If you really need to search for such common words, it would be better to search using IN BOOLEAN MODE instead, which does not observe the 50% threshold.

· To change the operators used for boolean full-text searches, set the ft_boolean_syntax system variable. This variable can be changed while the server is running, but you must have the SUPER privilege to do so. No rebuilding of indexes is necessary in this case.

· If you want to change the set of characters that are considered word characters, you can do so in two ways. Suppose that you want to treat the hyphen character (‘-’) as a word character. Use either of these methods:

o Modify the MySQL source: In myisam/ftdefs.h, see the true_word_char() and misc_word_char() macros. Add '-' to one of those macros and recompile MySQL.

o Modify a character set file: This requires no recompilation. The true_word_char() macro uses a “character type” table to distinguish letters and numbers from other characters. . You can edit the <ctype><map> contents in one of the character set XML files to specify that '-' is a “letter.” Then use the given character set for your FULLTEXT indexes.

After making the modification, you must rebuild the indexes for each table that contains any FULLTEXT indexes.

If you modify full-text variables that affect indexing (ft_min_word_len, ft_max_word_len, or ft_stopword_file), or if you change the stopword file itself, you must rebuild your FULLTEXT indexes after making the changes and restarting the server. To rebuild the indexes in this case, it is sufficient to do a QUICK repair operation:

mysql> REPAIR TABLE tbl_name QUICK;

Each table that contains any FULLTEXT index must be repaired as just shown. Otherwise, queries for the table may yield incorrect results, and modifications to the table will cause the server to see the table as corrupt and in need of repair.

Note that if you use myisamchk to perform an operation that modifies table indexes (such as repair or analyze), the FULLTEXT indexes are rebuilt using the default full-text parameter values for minimum word length, maximum word length, and stopword file unless you specify otherwise. This can result in queries failing.

The problem occurs because these parameters are known only by the server. They are not stored in MyISAM index files. To avoid the problem if you have modified the minimum or maximum word length or stopword file values used by the server, specify the same ft_min_word_len, ft_max_word_len, and ft_stopword_file values to myisamchk that you use for mysqld. For example, if you have set the minimum word length to 3, you can repair a table with myisamchk like this:

shell> myisamchk --recover --ft_min_word_len=3 tbl_name.MYI

To ensure that myisamchk and the server use the same values for full-text parameters, place each one in both the [mysqld] and [myisamchk] sections of an option file:

[mysqld]

ft_min_word_len=3

[myisamchk]

ft_min_word_len=3

An alternative to using myisamchk is to use the REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE, or ALTER TABLE statements. These statements are performed by the server, which knows the proper full-text parameter values to use.

InnoDB Performance Tuning Tips

InnoDB Performance Tuning Tips

• In InnoDB, having a long PRIMARY KEY wastes a lot of disk space because its value must be stored with every secondary index record. Create an AUTO_INCREMENT column as the primary key if your primary key is long.

• If the UNIX top tool or the Windows Task Manager shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound. Maybe you are making too many transaction commits, or the buffer pool is too small. Making the buffer pool bigger can help, but do not set it equal to more than 80% of physical memory.

• Wrap several modifications into one transaction. InnoDB must flush the log to disk at each transaction commit if that transaction made modifications to the database. The rotation speed of a disk is typically at most 167 revolutions/second, which constrains the number of commits to the same 167th of a second if the disk does not “fool” the operating system.

• If you can afford the loss of some of the latest committed transactions if a crash occurs, you can set the innodb_flush_log_at_trx_commit parameter to 0. InnoDB tries to flush the

log once per second anyway, although the flush is not guaranteed.

• Make your log files big, even as big as the buffer pool. When InnoDB has written the log files full, it has to write the modified contents of the buffer pool to disk in a checkpoint. Small log files cause many unnecessary disk writes. The drawback of big log files is that the recovery time is longer.

• Make the log buffer quite large as well (on the order of 8MB).

• Use the VARCHAR data type instead of CHAR if you are storing variable-length strings or if the column may contain many NULL values. A CHAR(N) column always takes N characters to store data, even if the string is shorter or its value is NULL. Smaller tables fit better in the buffer pool and reduce disk I/O. When using row_format=compact (the default InnoDB record format in MySQL 5.0) and variable-length character sets, such as utf8 or sjis, CHAR(N) will occupy a variable amount of space, at least N bytes.

• In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() call (which InnoDB uses by default) and other similar methods is surprisingly slow. If you are dissatisfied with database write performance, you might try setting the innodb_flush_method parameter to O_DSYNC. Although O_DSYNC seems to be slower on most systems, yours might not be one of them.

• When using the InnoDB storage engine on Solaris 10 for x86_64 architecture (AMD Opteron), it is important to mount any filesystems used for storing InnoDB-related files using the forcedirectio option. (The default on Solaris 10/x86_64 is not to use this option.) Failure to use forcedirectio causes a serious degradation of InnoDB‘s speed and performance on this platform. When using the InnoDB storage engine with a large innodb_buffer_pool_size value on any release of Solaris 2.6 and up and any platform (sparc/x86/x64/amd64), a significant performance gain can be achieved by placing InnoDB data files and log files on raw devices or on a separate direct I/O UFS filesystem (using mount option forcedirectio; see mount_ufs(1M)). Users of the Veritas filesystem VxFS should use the mount option convosync= direct. Other MySQL data files, such as those for MyISAM tables, should not be placed on a direct I/O filesystem. Executables or libraries must not be placed on a direct I/O filesystem.

• When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET AUTOCOMMIT and COMMIT statements: SET AUTOCOMMIT=0;

… SQL import statements …

COMMIT;

If you use the mysqldump option –opt, you get dump files that are fast to import into an InnoDB table, even without wrapping them with the SET AUTOCOMMIT and COMMIT statements.

• Beware of big rollbacks of mass inserts: InnoDB uses the insert buffer to save disk I/O in inserts, but no such mechanism is used in a corresponding rollback. A disk-bound rollback cantake 30 times as long to perform as the corresponding insert. Killing the database process does not help because the rollback starts again on server startup. The only way to get rid of a runaway rollback is to increase the buffer pool so that the rollback becomes CPU-bound and runs fast, or to use a special procedure.

• Beware also of other big disk-bound operations. Use DROP TABLE and CREATE TABLE to empty a table, not DELETE FROM tbl_name.

• Use the multiple-row INSERT syntax to reduce communication overhead between the client and the server if you need to insert many rows:

INSERT INTO yourtable VALUES (1,2), (5,5), …;

This tip is valid for inserts into any table, not just InnoDB tables.

• If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

SET UNIQUE_CHECKS=0;

… import operation …

SET UNIQUE_CHECKS=1;

For big tables, this saves a lot of disk I/O because InnoDB can use its insert buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys. UNIQUE_CHECKS allows but does not require storage engines to ignore duplicate keys.

• If you have FOREIGN KEY constraints in your tables, you can speed up table imports by turning the foreign key checks off for the duration of the import session:

SET FOREIGN_KEY_CHECKS=0;

… import operation …

SET FOREIGN_KEY_CHECKS=1;

For big tables, this can save a lot of disk I/O.

• If you often have recurring queries for tables that are not updated frequently, use the query cache:

[mysqld]

query_cache_type = ON

query_cache_size = 10M

• Unlike MyISAM, InnoDB does not store an index cardinality value in its tables. Instead, InnoDB computes a cardinality for a table the first time it accesses it after startup. With a large number of tables, this might take significant time. It is the initial table open operation that is important, so to “warm up” a table for later use, you might want to use it immediately after start up by issuing a statement such as SELECT 1 FROM tbl_name LIMIT 1.