Hi all, trying to optimise a table using some indexes but with not much luck, wondering if anyone had any pointers? The table is 1.5 million rows big, runing on mysql 5, MyISAM. I have a primary key and an index on 2 columns, col1 is a integer with length 4 and col2 is a varchar with length 70. I used: ALTER TABLE tablename ADD INDEX(col1(4),col2(30)); and the query giving me problems is: SELECT * FROM tablename where col1=3562 and col2='sometext' this takes anything from 5 to 15 seconds to run. Anything obvious i'm missing here?
Try looking at this. IMO, using a 70 char index in 1.5 mil records may be voodoo. Perhaps you can make it more efficient with the info at this link. http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html