Getting the SQL Query for Magento Fulltext Search

Short version: Magento searches through the catalogsearch_fulltext table. Reindex the Catalog Search Index if the data in there seems to be incorrect.

Long version: Ever searched for a keyword of Magento and wondered, “why did this product show up under this keyword search?” If you go to the catalog/product/list.phtml and echoed out the SQL query for the Product Collection (via $_productCollection->getSelectSql(true)), you’ll get a useless SQL query that looks something like this:

SELECT 
	1 AS `status`, <....> 
FROM `catalog_product_flat_1` AS `e`
	INNER JOIN `catalogsearch_result` AS `search_result` ON 
		search_result.product_id=e.entity_id AND 
		search_result.query_id='19091'
	INNER JOIN `catalog_product_index_price` AS `price_index` ON 
		price_index.entity_id = e.entity_id AND 
		price_index.website_id = '1' AND 
		price_index.customer_group_id = '1'
	INNER JOIN `catalog_category_product_index` AS `cat_index` ON 
		cat_index.product_id=e.entity_id AND 
		cat_index.store_id='1' AND 
		cat_index.visibility IN(3, 4) AND 
		cat_index.category_id='2' 
ORDER BY `position` asc, `e`.`name` asc LIMIT 50

So, you can see that query results are stored in the catalogsearch_result table, but how is that table built in the first place?

If you go to Mage_CatalogSearch_Model_Resource_Fulltext (or Mage_CatalogSearch_Model_Mysql4_Fulltext if you’re using an older version of Magento), and scroll down to the prepareResult() function, you can see it building the search query if that keyword hasn’t been searched before (if (!$query->getIsProcessed())). To get the query itself, you can add Mage::log($sql); Mage::log($bind); just before $adapter->query($sql, $bind);. Remember, this will only fire on a keyword that hasn’t been searched before, unless you add a || true on the getIsProcessed() line.

The log will show something like this:

INSERT INTO `catalogsearch_result` 
	(SELECT STRAIGHT_JOIN '19091', 
		`s`.`product_id`, 
		MATCH (`s`.`data_index`) AGAINST (:query IN BOOLEAN MODE), 
		c.position 
	FROM 
		`catalog_category_product` as `c`,  
		`catalogsearch_fulltext` AS `s` INNER JOIN `catalog_product_entity` AS `e` ON 
			`e`.`entity_id`=`s`.`product_id` 
	WHERE 
		((`s`.`data_index` LIKE :likew0)) AND 
		`s`.`store_id`='1' and 
		s.product_id = c.product_id) 
ON DUPLICATE KEY UPDATE `relevance`=VALUES(`relevance`)

So, you can see that the query checks the catalogsearch_fulltext table. For each product id, it stores a concatenated string of all the data that’s been indexed. It’ll look something like this:

fulltext_id	product_id	store_id	data_index
4		1		1		123456789|Taxable Goods|Laptop|Laptop Description|Laptop Description|1200|1

To control which data fields are indexed in this table, go to the Magento Admin and go to Catalog > Attributes > Manage Attribute. Select an attribute, and then scroll down to Frontend Properties. Toggle the “Use in Quick Search” settings. Then, reindex the Catalog Search Index if necessary.

Magento Community Edition to Enterprise Edition Version Mapping

Magento Enterprise Edition (EE) is essentially an extension of the Community Edition (CE). Enterprise Edition is a superset of the features of the Community Edition. That is, there no features in Community Edition that aren’t also in Enterprise Edition.

You can see that when you look at the folder tree.

Magento Enterprise Edition Folder Tree
The core Magento files are located in app/code/core/Mage, while all Enterprise related features are in app/code/core/Enterprise.

As such, you can roughly determine the corresponding CE edition based on your version of EE. Here’s a mapping, based on the release dates of each. It doesn’t cover the minor revisions in between, just major versions. I will attempt to keep this updated as new versions come out.

Community Edition Enterprise Edition
Magento CE 1.9 (5/13/14) Magento EE 1.14 (5/13/14)
Magento CE 1.8 (12/11/13) Magento EE 1.13 (10/17/13)
Magento CE 1.7 (4/24/12) Magento EE 1.12 (4/24/12)
Magento CE 1.6 (8/18/11) Magento EE 1.11 (8/18/11)
Magento CE 1.5 (2/8/11) Magento EE 1.10 (2/8/11)
Magento CE 1.4 (2/12/10) Magento EE 1.9 (7/19/10)
Magento EE 1.8 (4/14/10)
Magento EE 1.7 (1/19/10)
Magento CE 1.3 (3/30/09) Magento EE 1.6 (10/30/09)

As you can see, there is some overlap between the base Magento versions of EE 1.7-1.9, most likely because the Magento team were focusing their efforts on developing Enterprise Edition. The next parallel release was Magento CE 1.5 and EE 1.10, and they were kept pretty much in sync since then.

I found such a mapping helpful when I was looking for bug fixes for the core Magento code. I could say, I’m running EE 1.x, and there’s a bug in the app/code/core/Mage directory, so I can find the information about the bug in the corresponding CE edition.

Sources:
Magento Enterprise and Magento Community – The Key Differences
Infographic – Magento History and Evolution