NYCPHP Meetup

[Dan Cech]

Ok,

IF you want anyone to be able to help you out, you're going to have to 
give us some of the details of your particular problem, or a least some 
table schema.

That said, you might want to look into turning your inner joins into 
LEFT JOINs with the WHERE clauses moved into ON clauses for the joins, I 
suspect that this will yield a large speed increase.

After that I would examine the output of EXPLAIN to check on which 
indexes the query is using, and consider optimizing the table structure 
if this is inefficient.

Dan

Stephen Musgrave wrote:
> So, I thought this query would be easy, but I've spent the better part 
> of an afternoon on it.  To put it mildly:  it's very slow.  Any tips on 
> how i might speed it up would be greatly appreciated.
> 
> SELECT
>     a.column1
>     COUNT(DISTINCT c.column1) as column1_count
> FROM
>     table1 a,
>     table2 b,
>     table3 d,
>     table4 h
> WHERE
>     a.column2 = 1 AND a.column3 = 1
>     AND (c.column3 = a.column3 AND c.column2 = a.column2)
>     AND (d.column3 = a.column3 AND d.column2 = a.column2)
>     AND (h.column3 = a.column3 AND h.column2 = a.column2)
> GROUP BY
>     a. column3
> HAVING
>     (column1_count >= 2)
> 
> Thanks,
> 
> Stephen
> 
> _______________________________________________
> New York PHP Talk
> Supporting AMP Technology (Apache/MySQL/PHP)
> http://lists.nyphp.org/mailman/listinfo/talk
> http://www.newyorkphp.org