Home / Questions / 464642
Accepted
Eduardo Jiménez
Asked: 2022-06-25 20:33:09 +0800 CST 2022-06-25 20:33:09 +0800 CST

MariaDb: Improve query performance with LIKE

  • 772

For my project I need to query with LIKE . Reading the part of optimazing like it only mentions that optimizer_use_condition_selectivity should be used , however, the documentation is not very clear to me. I don't understand what should be done or how that optimization should be applied; So far it seems like a flag to me. But of what?

Also reading this other article (But SQL not MariaDb) recommend creating an index in this way.

CREATE NONCLUSTERED INDEX SK01_Test_Collation_SQL ON table_name (column_name) WITH(FILLFACTOR=90)

Should I get one for my table too or would the above be enough? How would it be for MariaDb in that case?

As additional information, this is what my table looks like (I removed all the foreign keys) and the query will be on the title column .

DROP TABLE IF EXISTS Thesis_Detail;
CREATE TABLE Thesis_Detail(
    thesis_id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    file VARCHAR(255) NOT NULL,
    abstract TEXT NOT NULL,
    uploaded_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX(title)
) DEFAULT CHARACTER SET utf8mb4;
mariadb

1 Answers

  1. Best Answer

    what I found and forgot to post is that making an index for text search only works for queries of the style LIKE 'Texto%', which is not useful for the purpose of doing a full search ( '%Texto%').

    So it's better to make use of FULLTEXT , which allows you to search by chunks.

    CREATE OR REPLACE TABLE Thesis_Detail(
    thesis_id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    file VARCHAR(255) NOT NULL,
    abstract TEXT NOT NULL,
    uploaded_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT(title)
) DEFAULT CHARACTER SET utf8mb4 ENGINE=MyISAM;

    This is how the table would look then and a query would look like:

    SELECT * WHERE MATCH(title) AGAINST(? IN NATURAL LANGUAGE MODE)
    • 0