phpmyadmin Question posted in
The official documentation can be found here.

Quick way to find a word using a SQL query – Phpmyadmin

My current code is to try to find 2 words "Red Table" in Title:

SELECT `id`,`title`,`colors`, `child_value`, `vendor`,`price`, `image1`,`shipping` 
FROM `databasename`.`the_table` WHERE 
`display` = '1' and  (`title` REGEXP '([[:blank:][:punct:]]|^)RED([[:blank:][:punct:]]|$)') 
                and  (`title` REGEXP '([[:blank:][:punct:]]|^)TABLE([[:blank:][:punct:]]|$)')

The problem is, this is so slow! I even put the status "Index" to the column Title.
I just want to search for multiple words in one (I would prefer in title AND description), but obviously I can’t use LIKE because it has to be separated by space or dash or start or end with that word etc.

I tried chat or something like that but phpmyadmin said function doesn’t exist.
Any suggestions?

Tags:

2

Answers


  1. 0

    You can not employ regular index for LIKE or REGEXP. Use Full Text Search for this. You can create FULLTEXT index on many columns and search them all in a single expression.

    CREATE TABLE the_table(
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 
title VARCHAR(200), 
description TEXT, 

...

FULLTEXT(title,description)
) ENGINE=InnoDB;

    And write query like this:

    SELECT * FROM the_table WHERE MATCH(title , description )
AGAINST('+RED +TABLE' IN BOOLEAN MODE) -- + means that a word must be present in each row that is returned

    Read more about usage and options: MySQL Full text search

    Login or Signup to reply.
  2. 0

    Plan A: MySQL pre-8.0: [[:<:]]RED[[:>:]] is a simplification. Those codes mean "word boundary", which encompasses space, punctuation, and start/end.

    Plan B: MySQL 8.0: \bRED\b is the new phrasing of the Plan A.

    Plan C: FULLTEXT(title) with AND MATCH(title) AGAINST('+RED +TABLE' IN BOOLEAN MODE)

    Plan D: If you need specifically "RED TABLE" but not "BLUE TABLE AND A RED CHAIR", then use this technique:

      AND MATCH(title) AGAINST('+RED +TABLE' IN BOOLEAN MODE)`
  AND LIKE '%RED TABLE%';

    Note on Plan D: The fulltext search is very efficient, hences it is done first. Then other clauses are applied to the few resulting rows. That is the cost of LIKE (or a similar REGEXP) is mitigated by having to check many fewer rows.

    Note: We have not discussed "red table" versus "red tables"

    By having suitable collation on the column title, you can either limit to the same case as the argument of have "Red" = "RED" = "red" = …

    Plan E: (To further fill out the discussion): FULLTEXT(title) with AND MATCH(title) AGAINST('+"red table" IN BOOLEAN MODE) should match only "red" and "table" in that order and next to each other.

    In general…

    • Use ENGINE=InnoDB, not MyISAM.
    • It is not really practical to set the min word len to 1 or 2. (3 is the default for InnoDB; and all settings have different names.)
    • If your hosting provider does not allow any my.cnf changes, change providers.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search