Changing search

Changing search

UPDATE post
SET post_content_filtered = REPLACE(
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(post_title, ":", ""),
                        "?", ""
                    ),
                    "#", ""
                ),
                "/", ""
            ),
            "-", ""
        ),
        " ", ""
    ),
    ".", ""
);

While keeping the ranking

• old: 1.2 Seconds

• new: 0.8151 Seconds

SELECT 
  ID, 
  post_title, 
  post_excerpt, 
  post_status, 
  post_type, 
  post_name, 
  post_content_filtered, 
  (
    CASE 
      WHEN post_title LIKE '1585DM4UBJM2%' THEN 10 
      ELSE 0 
    END
  ) + (
    MATCH (post_title) AGAINST (
      '1585D M4UBJM 25*' IN BOOLEAN MODE
        
    )
  ) AS search_rank 
FROM 
  obso_posts 
WHERE 
  post_status = 'publish' 
  AND post_type = 'product'
  AND (
    post_content_filtered LIKE '1585DM4UBJM2%'
      
     OR MATCH (post_title) AGAINST (
      '1585D M4UBJM 25*' IN BOOLEAN MODE
    ) > 0
    
  )
ORDER BY 
  search_rank DESC;

Some form of ranking Selected to implement response: 0.0046 Second

SELECT ID, post_title, post_excerpt, post_status, post_type, post_name, post_content_filtered,
    
(CASE WHEN post_title
LIKE "1585DM4UBJM2%" THEN 10 ELSE 0 END) +

    (MATCH (post_title) AGAINST ("'1585D M4UBJM 25*'" IN BOOLEAN MODE)) AS search_rank
FROM obso_posts
WHERE post_content_filtered
LIKE "1585DM4UBJM2%"
ORDER BY search_rank DESC
LIMIT 5;

without ranking and only matching to left response:0.0028

SELECT ID, post_title, post_excerpt, post_status, post_type, post_name, post_content_filtered
    
FROM obso_posts
WHERE post_content_filtered
LIKE "1585DM4UBJM2%"

LIMIT 5;