Table lock with join.

[Fournier Jocelyn [Presence-PC]]

Hi,

Is there any way to avoid table lock with a join ?

E.g. :

SELECT /*!40001 SQL_CALC_FOUND_ROWS */
forumconthardwarefr7.titre,searchhardwarefr7.numeropost,SUM(MATCH
(searchhardwarefr7.contenu) AGAINST ('test')) as r FROM
searchhardwarefr7,forumconthardwarefr7 WHERE MATCH
(searchhardwarefr7.contenu) AGAINST ('test' IN BOOLEAN MODE) AND
forumconthardwarefr7.numeropost=searchhardwarefr7.numeropost GROUP BY
searchhardwarefr7.numeropost ORDER BY r DESC LIMIT 0, 30;

This query is locking the table forumconthardwarefr7. The problem is the
search in searchhardwarefr7 could be very long, and I couldn't lock
forumconthardwarefr7, because it's heavily used.

A workaround could be to do :

SELECT /*!40001 SQL_CALC_FOUND_ROWS */
searchhardwarefr7.numeropost,SUM(MATCH (searchhardwarefr7.contenu) AGAINST
('test')) as r FROM searchhardwarefr7 WHERE MATCH
(searchhardwarefr7.contenu) AGAINST ('test' IN BOOLEAN MODE) GROUP BY
searchhardwarefr7.numeropost ORDER BY r DESC LIMIT 0, 30;

SELECT titre FROM forumconthardwarefr7 WHERE numeropost IN (the list of
matching id);

This is perfect because it doesn't lock the forumconthardwarefr7 table, but
the problem is I loose the sorting on SUM(MATCH (searchhardwarefr7.contenu)
AGAINST ('test')) (because IN doesn't care about the order of ids).

Is there a smarter way to do this (or a way to specify to not lock a table
in a join - perhaps a new option ?).

Thanks and regards,
  Jocelyn Fournier