Обсуждение: looking to speed up SELECT ... LEFT JOIN query

Hi,

I have a working SELECT statement using LEFT JOIN but I'm hoping
someone can suggest something that might be a bit quicker (currently 3
seconds for result).

SELECT * FROM ((table1 LEFT JOIN table2 USING (custno)) AS j1
         LEFT JOIN table3 USING (custno)) AS j2
         LEFT JOIN table4 USING (custno)
         WHERE custno='$custno';

The primary table is table1, the key for all tables is 'custno'.  I
need the LEFT JOIN because any one or all of the other tables (table2,
table3, table4) might not have corresponding data.

Regards,

Trey Ackerman

On Wednesday 07 Aug 2002 6:03 am, Trey Ackerman wrote:
> Hi,
>
> I have a working SELECT statement using LEFT JOIN but I'm hoping
> someone can suggest something that might be a bit quicker (currently 3
> seconds for result).
>
> SELECT * FROM ((table1 LEFT JOIN table2 USING (custno)) AS j1
>          LEFT JOIN table3 USING (custno)) AS j2
>          LEFT JOIN table4 USING (custno)
>          WHERE custno='$custno';
>
> The primary table is table1, the key for all tables is 'custno'.  I
> need the LEFT JOIN because any one or all of the other tables (table2,
> table3, table4) might not have corresponding data.

We'll need to see the output of an EXPLAIN / EXPLAIN ANALYSE - also could you
join the tables in a different order, smallest first?

- Richard Huxton