I am need of some MYSQL optimization advice. I have a table that contains 250,000+ plus rows and when I try to select one at random I get execution times of 60+ seconds.

Here is the SQL I am using:

SELECT DISTINCT * FROM table WHERE col_a = 0123456789 AND col_b = "TYPE"

Both col_a and col_b are indexes, neither are primary because there exists duplicate values for each column.

I need some help to get this number down to a more pratical number.

Distinct is very expensive. If you can specify the columns you want instead of using * that will help to some degree. Also consider a compound index with both of the columns in your where clause.

By removing DISTINCT it decreases by about 5-10 seconds, I have not yet tried specifying the columns. Also I am not familiar with compound indexes, I will have research those.

Take a look at the execution plan with and without the distinct operator...

A compound index is one that has more than one column. If you put the two of the columns in your where clause in an index - in the same order they're used in the query - the server won't have to read the table rows to find the records that meet your criteria, only to gather them. It's probably using the first column/index and then evaluating all of the matches against the rest of your where clause. You can cut out all of the work from "false positives" this way.

Also higher cardinality - mostly unique entries - in an index makes it more efficient.

Thanks Forrest, that worked great.