How to change Magento search from LIKE ‘keyword’ to LIKE ‘keyword%’ but not LIKE ’%keyword% ?

The Like search is the most basic routine available in a SQL database engine that can be called a search function. It has to be applied against the data_index field in catalogsearch_fulltext and due to the fact that it is searching the whole field, has to be a sliding window search, or simulated simple regex if you’re used to those terms.

Here’s the code snippet that creates it:

foreach ($words as $word) {
                    $like[] = '`s`.`data_index` LIKE :likew' . $likeI;
                    $bind[':likew' . $likeI] = '%' . $word . '%';
                    $likeI ++;
                }

The reason for using %$word%; for the match string is pretty obvious and simple. It has to match against the whole field and find something bounded by spaces (definition of a word) to get a return match.

So a search on relation will return a match on: 

relation
correlation
correlations
   relations
   relationship
ad nauseum

If you remove the % from the beginning of the match string, then it has to match everything up to $word in the data_index field in order to return a match, so no solution there.

The next step is to decide that your customer might be smart enough to start a word with the right spelling, but trail off on the right hand side to unfindability. If Magento’s like search is modified to look for words starting with a space, looking for relation should no longer also return correlation, correlations, etc.

Where this modification falls down on the job is that it will never match the beginning of the data_index field, starting words in a parenthesis block or words that have been separated that are really one word or hyphenated.

For example, searches for black and stone will no longer return blackstone.

Modification to experiment with this this can be done in app/code/local/Mage/CatalogSearch/Model/Mysql4/Fulltext.php as follows: 

if ($searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_LIKE
                || $searchType == Mage_CatalogSearch_Model_Fulltext::SEARCH_TYPE_COMBINE) {
                $words = $stringHelper->splitWords($queryText, true, $query->getMaxQueryWords());
                $likeI = 0;
                foreach ($words as $word) {
                    $like[] = '`s`.`data_index` LIKE :likew' . $likeI;
                    $bind[':likew' . $likeI] = '% ' . $word . '%';
                    $likeI ++;
                }
                if ($like) {
                    $likeCond = '(' . join(' AND ', $like) . ')';
                }
            }

From

'%' . $word . '%'; to
'% ' . $word . '%';