EverSQL Database Performance Knowledge Base

Question 1

MySQL - Change index on myIsam table

Database type:

I have a table where two columns are used in a where condition.

This is a MyIsam table and both columns hold text and use FULLTEXT as index.

The values in both columns are not unique.

The select statement works pretty slow.

Question is: can I simply remove the FULLTEXT index and use another index instead?

The query that is used is just as simple as possbile:

SELECT * FROM tbl WHERE col1=X AND col2=y and col3=z

Thanks!

Answer

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

ALTER TABLE `tbl` ADD INDEX `tbl_idx_col1_x_col2_y_col3_z` (`col1`,`X`,`col2`,`y`,`col3`,`z`);

SELECT
        * 
    FROM
        tbl 
    WHERE
        tbl.col1 = tbl.X 
        AND tbl.col2 = tbl.y 
        AND tbl.col3 = tbl.z

Question 2