/dev/weblog

Another log on the fire

Posted by Double Compile. Monday, April 21. 2008, 08:10 PM in Databases

If you want foreign key support in MySQL, you need to use the InnoDB storage engine. If you want fulltext index support, you need to use the MyISAM storage engine. Ain't that a bitch?

Just for the record: PostgreSQL supports them both well.

To be fair, the syntax for fulltext searching in MySQL is more simple than that of fulltext searching in PostgreSQL. Here's an example:

MySQL:

SELECT title
FROM example
WHERE MATCH (title, body) AGAINST ('foobar')

PostgreSQL:

SELECT title
FROM example
WHERE to_tsvector(title || body) @@ to_tsquery('foobar');

So while the syntax is easier, you can't use fulltext and foreign keys simultaneously in MySQL. PostgreSQL wins this battle.