How magento default search is working

Default magento Search :

“Catalogsearch_fulltext”  table is the main table in magento default search.All the searchig values will be store in this table only.

1. Search with Old value (keyword)  :

If your search with old keyword which will be search by some one, then the magento won’t create new row from this table. Directly it will refer this table and fetch as a collection and return it.

2.Search with New value (keyword):

If you are searching with new keyword (ie) earlier no one is used this keyword for searching then it will create new row in this table. Using that keyword, magento fetch all the value from appropriate table and it will stored in to Catalogsearch_fulltext table. The format will be like below in Data_index column

 Sku|brand|status|tax class id|manufacturer|name|description|short_description|price|0 (or) 1

How to Refer table for default magento search:

Step:1

Table Name catalogsearch_fulltext :

  1. First you have to refer “catalogsearch_fulltext” table and column name is (data_index)

Data_index -> How it will be store in DB. Example format is

Sku|brand|status|tax class id|manufacturer|name|description|short_description|price|0 (or) 1

  1. Here You will get [product_id]. With this id you will get all the details from appropriate table’s.

  2. Some of the basic information about the product, you will get it from this row itself.

Step: 2

How to get Product Name :

Query: 1

Table ->eav_attribute

SELECT attribute_id  FROM eav_attribute WHERE `attribute_code` LIKE ‘name’ and entity_type_id=4

Query : 2

Table-> catalog_product_entity_varchar

  1. eav_attribute.attribute_id = 63

  2. catalogsearch_fulltext.product_id = 43  // for example

SELECT value FROM `catalog_product_entity_varchar` WHERE `attribute_id` =63 AND  entity_id` =43

Step: 3

For getting other details (for example brand, price,gender,category), follow below step

Brand: (You will get brand details from this table)

Have to reffer below table (with column name)

1. catalogsearch_fulltext [Need to check column called  “product_id” ],

2.  catalog_product_entity_int [  check with this column  entity_id && attribute_id = 163 ] (need to get column called “value”),

3. eav_attribute_option_value [check with this column  “option_id” ] (need to get column name called  “value” )

 Ex : SELECT attribute_id FROM `eav_attribute` WHERE `attribute_code` = ‘brand’ and entity_type_id = 4

 Note: here 163 is called Example brand ID (as per my example).

 Price : (You will get Price details from this table)

1. catalogsearch_fulltext [Need to check column called  “product_id” ]

2. catalog_product_index_price [ check with this column “entity_id”  and customer_group_id = 1] (need to get column name called “price , final_price”)

Note: Now you can use Customer_group_id = 1.(as per my group ID,it may change for you)

 Gender: (You will get gender details from this table)

1. catalogsearch_fulltext [Need to check column called  “product_id” ]

2.  catalog_product_entity_int [  check with this column  entity_id && attribute_id = 164  ] (need to get column called “value”)

3.   eav_attribute_option_value [check with this column  “option_id” ] (need to get column name called  “value” )

 Ex : SELECT attribute_id FROM `eav_attribute` WHERE `attribute_code` = ‘gender’ and entity_type_id = 4

 Note: here 164 is called gender ID (as per my example,it may change for you)

If you found anything wrong in this post please let me know, it could be helpful for me to correct.

By senthil/Thamu