Search code examples
phpmysqlphpmyadminfull-text-searchsql-like

Most efficient way of MySQL rows?

I know very little of MySQL. Right now I've a table with close to 1,000,000 entries and I wish to search for a particular string in a particular column and return the columns.

I've seen LIKE but I've read it slow for such huge entries. I've heard Fulltext-Indexing it a better option for searching.

I use phpMyAdmin for viewing my MySQL database. I do not know what exact modifications I need to make to my table and the function that should be used thereafter?

PS: Also, is it possible to pattern match with AGAINST as in LIKE

Solution

  • You can use FULLTEXT index to speed up searching the table. It has, however, some limitations - it works only on MyISAM tables (unless you are using MySQL 5.6.4 or newer), you need to specify it over all columns that you want to search at once and you can only search for words that are longer than a limit set globally to whole database server (default is 4 characters)

    Either use PhpMyAdmin to create the index, or you can do it manually, the syntax is rather simple:

    ALTER TABLE tablename ADD FULLTEXT (column_you_want_to_search);

    Or for more columns:

    ALTER TABLE tablename ADD FULLTEXT (column1, column2, column3);

    Then you can ask the database, which rows contains some words:

    SELECT * FROM tablename WHERE MATCH(column_you_want_to_search) AGAINST ('search query')
SELECT * FROM tablename WHERE MATCH(column1, column2, column3) AGAINST ('another query')

    There is also option to search in "boolean mode", where you can specify which words you want, don't want or use asterisks as wildcards. See this MySQL reference for more.