[LRUG] Speeding up a SQL query

[Matt Gibson]

Hi Andy

I’ve fixed a similar problem before by indexing one column from the WHERE clauses (the one with the highest cardinality), so that the number of rows is reduced drastically by the index search before the other columns are scanned for matches. I imagine that by indexing one or two of those columns, you ought to be able to speed it up a lot. Running a quick query like the following should clarify which columns to use:

SELECT COUNT(DISTINCT col1), AS col1_uniques, COUNT(DISTINCT col2), AS col2_uniques, … etc
FROM table

Of course if all columns have low cardinality, then this may not help, but it’s worth a try.

Hope that helps

Matt


On 21 Feb 2014, at 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