From: Neil Tompkins
Date: November 22 2012 5:35pm
Subject: Re: Basic SELECT help
List-Archive: http://lists.mysql.com/mysql/228700
Message-Id: <CADa_cYqtZGb7oi0UK2KDYJjXAikmZDj4odzuDjT6EcQC_6nErw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary=047d7b66f3c50781b404cf18e419

--047d7b66f3c50781b404cf18e419
Content-Type: text/plain; charset=ISO-8859-1

Doing a EXPLAIN on the SELECT statement it is using "Using where; Using
temporary; Using filesort" with 14000 rows of data.  How best to improve
this; when I already have indexed on id and type



On Thu, Nov 22, 2012 at 4:46 PM, Michael Dykman <mdykman@stripped> wrote:

> Assuming that (id,type) is unique in the source data, that is a pretty
> elegant method:
>
> > select id from
> > (select distinct id, count(*)
> > from my_table
> > where type in (2,5)
> > group by id
> > having count(*) = 2)a;
> >
>
> --
>  - michael dykman
>  - mdykman@stripped
>
>  May the Source be with you.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>
>

--047d7b66f3c50781b404cf18e419--