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
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
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