I think you want a UNION, not a JOIN. Availabilty of UNION depends on the version you are running.
Remi Mikalsen wrote:
Hello.
I have a problem I believe must have been solved lots of times before. I am doing a fulltext search on two tables at the same time. The problem is that it takes about 20-30 seconds (two really small tables! < 400 entries!!!).
Here is the query:
SELECT DISTINCT object.id_object FROM object , short_info, long_info WHERE object.owner='2' AND ((short_info.object=object.id_object AND MATCH (short_info.short_info) AGAINST ('any searchstring' IN BOOLEAN MODE)) OR (long_info.object=object.id_object AND MATCH (long_info.long_info) AGAINST ('any searchstring' IN BOOLEAN MODE)));
The columns long_info and short_info are both indexed with fulltext indexes.
If I divide the query in two, and execute the two fulltext boolean searches separately, they take about 0.1 seconds each! Is there a way to optimize the above query to improve performance, or is the only solution to execute two queries?
Remodeling the database is not possible in my case. The fulltext search is a small part of a new search engine for a database that has been on-line for over a year now.
Remi Mikalsen
E-Mail: [EMAIL PROTECTED] URL: http://www.iMikalsen.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
