StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSearch with "MATCH AGAINST" causes CakePHP app to crash
text
Body
copied!<p>I have two projects (A and B). They're both built on CakePHP framework, and basically, they got the same structure, meaning that both their databases have the same structure. Let's say they are identical. </p> <p>I have created a view, where I've put all the data needed to perform a search and I called it search_areas. Every column from the tables that are in the view are FULLTEXT indexes.</p> <p>Now, when I perform a search on Project A, it works like a charm. But when I do it on project B, it takes forever. Actually, something jams, something in the code. The database works fine but the app is unusable for about 10 minutes. Has anybody encountered such a problem?</p> <p>This is the sql that runs when a search is performed:</p> <pre><code>SELECT Product.*, MainImage.*, Currency.rate, Category.green_tax, CategoriesCategory.full_alias, (Product.price*Currency.rate + Category.green_tax)*1.24 as real_price, MATCH (SearchArea.Cname,SearchArea.Mname,SearchArea.Pname,SearchArea.description,SearchArea.special_description,SearchArea.model,SearchArea.part_number,SearchArea.series,SearchArea.color,SearchArea.big_string) AGAINST ('+search term' IN BOOLEAN MODE) as score FROM `products` AS `Product` LEFT JOIN currencies AS `Currency` ON (`Product`.`currency` = `Currency`.`code`) LEFT JOIN categories AS `Category` ON (`Product`.`category_id` = `Category`.`id`) LEFT JOIN manufacturers AS `Manufacturer` ON (`Product`.`manufacturer_id` = `Manufacturer`.`id`) LEFT JOIN categories_categories AS `CategoriesCategory` ON (`Product`.`category_id` = `CategoriesCategory`.`category_id`) LEFT JOIN search_areas AS `SearchArea` ON (`SearchArea`.`id` = `Product`.`id`) LEFT JOIN `product_images` AS `MainImage` ON (`MainImage`.`product_id` = `Product`.`id` AND `MainImage`.`main` = 1) WHERE MATCH (`SearchArea`.`Cname`,`SearchArea`.`Mname`,`SearchArea`.`Pname`,`SearchArea`.`description`,`SearchArea`.`special_description`,`SearchArea`.`model`,`SearchArea`.`part_number`,`SearchArea`.`series`,`SearchArea`.`color`,`SearchArea`.`big_string`) AGAINST ('+search term' IN BOOLEAN MODE) AND `Product`.`active` = 1 LIMIT 15 </code></pre> <p>Any ideas?</p> <pre><code>****** EDIT ***** </code></pre> <p>I ran the query in mysql and it seems that there's the problem. It takes a very long time (2-5 minutes). Seems that I have to optimize my query, maybe find another solution. Thanks for helping. If anybody has an idea how to use the MATCH ... AGAINST ... syntax efficiently on a view or some concatenated element please post. I will probably abandon the above solution.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload