Doctrine2 - use fulltext and MyIsam

Question

I am building an app in Symfony2, using Doctrine2 with mysql. I would like to use a fulltext search. I can't find much on how to implement this - right now I'm stuck on how to set the table engine to myisam.

It seems that it's not possible to set the table type using annotations. Also, if I did it manually by running an "ALTER TABLE" query, I'm not sure if Doctrine2 will continue to work properly - does it depend on the InnoDB foreign keys?

Is there a better place to ask these questions?

Granite

Answer 1

INTRODUCTION

Doctrine2 uses InnoDB which supports Foreign Keys used in Doctrine associations. But as MyISAM does not support this yet, you can not use MyISAM to manage Doctrine Entities.

On the other side, MySQL v5.6, currently in development, will bring the support of InnoDB FTS and so will enable the Full-Text search in InnoDB tables.

SOLUTIONS

So there are two solutions :

1. Using the MySQL v5.6 at your own risks and hacking a bit Doctrine to implement a MATCH AGAINST method : link in french... (I could translate if needed but there still are bugs and I would not recommend this solution)

2. As described by quickshifti, creating a MyISAM table with fulltext index just to perform the search on. As Doctrine2 allows native SQL requests and as you can map this request to an entity (details here).

EXAMPLE FOR THE 2nd SOLUTION

Consider the following tables :

table 'user' : InnoDB [id, name, email]
table 'search_user : MyISAM [user_id, name -> FULLTEXT]

Then you just have to write a search request with a JOIN and mapping (in a repository) :

<?php

public function searchUser($string) {

    // 1. Mapping
    $rsm = new ResultSetMapping();
    $rsm->addEntityResult('Acme\DefaultBundle\Entity\User', 'u');
    $rsm->addFieldResult('u', 'id', 'id');
    $rsm->addFieldResult('u', 'name', 'name');
    $rsm->addFieldResult('u', 'email', 'email');

    // 2. Native SQL
    $sql = 'SELECT u.id, u.name FROM search_user AS s JOIN user AS u ON s.user_id = u.id  WHERE MATCH(s.name) AGAINST($string IN BOOLEAN MODE)> 0;

    // 3. Run the query
    $query = $this->_em->createNativeQuery($sql, $rsm);

    // 4. Get the results as Entities !
    $results = $query->getResult();

    return $results;
}
?>

But the FULLTEXT index needs to stay up-to-date. Instead of using a cron task, you can add triggers (INSERT, UPDATE and DELETE) like this :

CREATE TRIGGER trigger_insert_search_user
AFTER INSERT ON user
FOR EACH ROW
INSERT INTO search_user SET user_id=NEW.id, name=NEW.name;

CREATE TRIGGER trigger_update_search_user
AFTER UPDATE ON user
FOR EACH ROW
UPDATE search_user SET name=name WHERE user_id=OLD.id;

CREATE TRIGGER trigger_delete_search_user
AFTER DELETE ON user
FOR EACH ROW
DELETE FROM search_user WHERE user_id=OLD.id;

So that your search_user table will always get the last changes.

Of course, this is just an example, I wanted to keep it simple, and I know this query could be done with a LIKE.

Growler

Looks good. I actually ended up sticking with Innodb and implementing Sphinx, which is working very well: sphinxsearch.com – Leningrad

Answer 2

Doctrine ditched the fulltext Searchable feature from v1 on the move to Doctrine2. You will likely have to roll your own support for a fulltext search in Doctrine2.

I'm considering using migrations to generate the tables themselves, running the search queries w/ the native SQL query option to get sets of ids that refer to tables managed by Doctrine, then using said sets of ids to hydrate records normally through Doctrine.

Will probly cron something periodic to update the fulltext tables.

Biddable