[LRUG] Speeding up a SQL query

[Max Williams]

What about writing a concatenated version of fields 1-10 into another field
and then indexing & searching on that?


On 21 February 2014 11:22, Andrew Stewart <boss at airbladesoftware.com> wrote:

> 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
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org
> http://lists.lrug.org/listinfo.cgi/chat-lrug.org
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20140221/7114f6a8/attachment-0003.html>