"DeRyl" <[EMAIL PROTECTED]> wrote on 09/23/2004 09:51:45 AM:
> The fact that you are joining 10 tables together in one query may be a
> major portion of your performance problem. Even if the *average* size of
> each table is only 10 rows, the MySQL engine will have to pour over
> 10x10x10x10x10x10x10x10x10x10 = 10 to the 10th power = 1e10 =
> 10,000,000,000 (10 billion) combinations of data rows before it gets to
> even think about some of your WHERE conditions.
>
> ## oh I understand...
>
> I have a few suggestions:
>
> FIRST - Rewrite your query to use the explicit INNER JOIN form of table
> joins and NOT the comma-join form you are currently using. The explicit
> form is much MUCH easier to double check that you have properly JOINed
> each table to each other table. Without properly defined join conditions
> you will create a Cartesian product between two or more of your tables.
> Cartesian products are rarely good things for query performance.
>
> ## can you show me an example?
> I'm not sure I understand correctly Your suggestion...
This is my attempt to convert your implicit comma joins into explicit
INNER JOIN statements. I also reformatted your query to get rid of
email-wrapping and applied some aliases to your table names.
SELECT DISTINCT
logo
, klient.klientid
, klientnazwa
, struktura
, concat(kodpocztowy,' ',miejscowosc) miasto
, aparatnumer
, concat(ulicaskrot,' ',ulicanazwa,' ',posesja) ulica
, concat('woj. ',wojewodztwo,' powiat:',powiat) wojpow
, klientbranza branza
, email
, www
, wizytowka
FROM klient
INNER JOIN klientulice
ON klient.klientid = klientulice.klientid
INNER JOIN klientulica
ON klientulice.klientulicaid= klientulica.klientulicaid
INNER JOIN klientmiejscowosci
ON klient.klientid = klientmiejscowosci.klientid
INNER JOIN klientmiejscowosc
ON
klientmiejscowosci.klientmiejscowoscid=klientmiejscowosc.klientmiejscowoscid
INNER JOIN klientbranza
ON klient.klientid = klientbranza.klientid
INNER JOIN branzaslowa
ON klientbranza.branzaid=branzaslowa.branzaid
INNER JOIN branzaslowo
ON branzaslowa.branzaslowoid=branzaslowo.branzaslowoid
INNER JOIN klientslowa
ON klient.klientid = klientslowa.klientid
INNER JOIN klientslowo
ON klientslowa.klientslowoid=klientslowo.klientslowoid
WHERE wojewodztwoid=7
AND klientulica.klientulica like 'dwo%'
AND klientmiejscowosc.klientmiejscowosc like'war%'
AND branzaslowo.branzaslowo like'sam%'
AND klientslowo.klientslowo LIKE 'sam%'
I staggered your sub-joins by one indention so that you could recognize
some possible points of creating little mini-joins later on a partial
result set. (See step 4)
>
> SECOND - Check your indexes. Make sure that you are joining tables on
> columns that appear first in at least one index. Carefully consider
> creating a multiple column index or two (especially if this is a common
> query and/or you use those conditions often).
>
> ## I usually use indexes on key fields [like klientid...] and on
launching
> tables [like klientslowa] one index on both two fields...
>
> THIRD - Consider evaluating this query in stages. Based on your re-write
> to eliminate the comma-joins, I can help you with that, too.
>
> ## ? what do You mean?
I mean do not try to do everything in one massive query. There are times,
especially when performing more than about 6 or 7 joins in a single query
that breaking one larger operation into two smaller ones is the more
efficient way to go.
I wish I knew more polish as it would help me to break up this query
better. What you want to do in each stage is to build a temporary table
that holds just enough information in it to build the next stage.
CREATE TEMPORARY TABLE tmpStage1 (KEY(klientid))
Select klient.klientid
, ... (all of the computed values that rely on data from any of
the tables participating in this stage or values to be used in future
stages)...
FROM klient
INNER JOIN klientulice
ON klient.klientid = klientulice.klientid
INNER JOIN klientulica
ON klientulice.klientulicaid= klientulica.klientulicaid
WHERE wojewodztwoid=7
AND klientulica.klientulica like 'dwo%'
CREATE TEMPORARY TABLE tmpStage2 (KEY(klientid))
SELECT t1.klientid
, t1.logo
, t1.klientnazwa
, ... (other t1.fields) ...
, ...(computed fields using values from these tables and values to
combine with values from the next stage's tables )
FROM tmpStage1 t1
INNER JOIN klientmiejscowosci
ON t1.klientid = klientmiejscowosci.klientid
INNER JOIN klientmiejscowosc
ON
klientmiejscowosci.klientmiejscowoscid=klientmiejscowosc.klientmiejscowoscid
WHERE klientmiejscowosc.klientmiejscowosc like'war%'
... repeat until you have added together the data from all but the very
last tables. Then you just SELECT the results and drop all of your
temporary tables. ....
SELECT t3.logo
, t3.klientid
, t3.klientnazwa
, t3.struktura
, t3.miasto
, t3.aparatnumer
, t3.ulica
, t3.wojpow
, t3.branza
, t3.email
, t3.www
, t3.wizytowka
FROM tmpStage3 t3
INNER JOIN klientslowa
ON klient.klientid = klientslowa.klientid
INNER JOIN klientslowo
ON klientslowa.klientslowoid=klientslowo.klientslowoid
WHERE klientslowo.klientslowo LIKE 'sam%'
DROP TABLE tmpStage1, tmpStage2, tmpStage3
That way you create smaller "join sets" so that each join happens much
faster. Compute what you can at each stage and keep any values you will
need for a later stage. Trust me, it's faster (much faster) if you do it
right. I will work with you as much as you need to get this going.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
> FOURTH - Consider using table aliases. It may make your SELECT and WHERE
> clauses easier to read.
>
> ## oh yes :) You're right...
>
> regards in advance
> Darek
>
