How is a frontend full-text search implemented?



















































































The current result of the $select->assemble():

SELECT 
	`search_index`.`entity_id` 
FROM 
		`catalogsearch_fulltext_scope1` AS `search_index`
	LEFT JOIN 
		`catalog_eav_attribute` AS `cea` 
	ON 
		search_index.attribute_id = cea.attribute_id
;




The current result of the $select->assemble():

SELECT 
	`search_index`.`entity_id` 
FROM 
		`catalogsearch_fulltext_scope1` AS `search_index`
	LEFT JOIN `catalog_eav_attribute` AS `cea` 
		ON search_index.attribute_id = cea.attribute_id
	LEFT JOIN `cataloginventory_stock_status` AS `stock_index` 
		ON 
				search_index.entity_id = stock_index.product_id 
			AND 
				stock_index.website_id = '1' 
WHERE (stock_index.stock_status = 1)

The select has not been changed in my case.










$query->assemble():

SELECT `search_synonyms`.* 
FROM `search_synonyms` 
WHERE (MATCH (synonyms) AGAINST ('alligator' IN BOOLEAN MODE));






The current result of the $select->assemble():

SELECT `search_index`.`entity_id` 
FROM 
		`catalogsearch_fulltext_scope1` AS `search_index`
	LEFT JOIN `catalog_eav_attribute` AS `cea` 
		ON search_index.attribute_id = cea.attribute_id
	LEFT JOIN `cataloginventory_stock_status` AS `stock_index` 
		ON 
			search_index.entity_id = stock_index.product_id 
			AND stock_index.website_id = '1' 
WHERE 
		(stock_index.stock_status = 1) 
	AND 
		(MATCH (data_index) AGAINST ('alligator*' IN BOOLEAN MODE))


$parentSelect->assemble()

SELECT 
	`main_select`.`entity_id`
	, MAX(score) AS `relevance` 
FROM (
	SELECT 
		`search_index`.`entity_id`
		, (
			(0) 
			+ LEAST((MATCH (data_index) AGAINST ('alligator*' IN BOOLEAN MODE)), 1000000) 
			* POW(2, search_weight)
		) AS `score` 
	FROM 
			`catalogsearch_fulltext_scope1` AS `search_index`
		LEFT JOIN 
			`catalog_eav_attribute` AS `cea` 
			ON search_index.attribute_id = cea.attribute_id
		LEFT JOIN `cataloginventory_stock_status` AS `stock_index` 
			ON 
					search_index.entity_id = stock_index.product_id 
				AND 
					stock_index.website_id = '1' 
	WHERE 
			(stock_index.stock_status = 1) 
		AND 
			(MATCH (data_index) AGAINST ('alligator*' IN BOOLEAN MODE))
) AS `main_select` 
GROUP BY `entity_id`

The select has not been changed in my case because of the $matchQueries absence.






The method returns:

INSERT INTO `search_tmp_5701d40e461c60_04941413`
	SELECT
		`main_select`.`entity_id`
		, MAX(score) AS `relevance`
	FROM (
		SELECT
			`search_index`.`entity_id`
			, (
				(0)
				+ LEAST((MATCH (data_index) AGAINST ('alligator*' IN BOOLEAN MODE)), 1000000)
				* POW(2, search_weight)
			) AS `score`
		FROM
				`catalogsearch_fulltext_scope1` AS `search_index`
			LEFT JOIN
				`catalog_eav_attribute` AS `cea`
				ON search_index.attribute_id = cea.attribute_id
			LEFT JOIN `cataloginventory_stock_status` AS `stock_index`
				ON
						search_index.entity_id = stock_index.product_id
					AND
						stock_index.website_id = '1'
		WHERE
				(stock_index.stock_status = 1)
			AND
				(MATCH (data_index) AGAINST ('alligator*' IN BOOLEAN MODE))
	) AS `main_select`
	GROUP BY `entity_id`
	ORDER BY `relevance` DESC
	LIMIT 10000

The query result: