[LRUG] Speeding up a SQL query

[Andrew Stewart]

Hello LRUG!

At various points in my Rails app I run a query to find any near-duplicates a record has:

SELECT col1, col2, ..., col10
FROM widgets
WHERE col1 = 'blah' AND col2 = 'blurgh' ... AND col10 = 'bleugh'
AND id <> 123456

There are about 200,000 widgets in the table so it's not massive.  But the query is somewhat sluggish.

When MySQL explains itself it says:

  select_type: SIMPLE
        table: widgets
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 84888
        Extra: Using where

If I were just querying one or two columns I'd index them but I'm not sure indexing 10 columns is possible or helpful (it would slow down writes).

Does anyone have any tips?

Thanks in advance,

Andy Stewart