help with proper conditions and indexes needed

[DeRyl]

hi again,

I have question like that:

SELECT /*! SQL_BUFFER_RESULT */
DISTINCT branza.branzaid, branza.branzanazwa
FROM branza, klientbranza, klientwojewodztwo
WHERE
branza.branzaid = klientbranza.branzaid
AND klientbranza.klientid = klientwojewodztwo.klientid
AND wojewodztwoid =9
ORDER BY bsort ASC

and

EXPLAIN SELECT /*! SQL_BUFFER_RESULT */ DISTINCT branza.branzaid,
branza.branzanazwa
FROM branza, klientbranza, klientwojewodztwo
WHERE branza.branzaid = klientbranza.branzaid AND klientbranza.klientid =
klientwojewodztwo.klientid AND wojewodztwoid =9
ORDER BY bsort ASC

shows me:

      table  type  possible_keys  key  key_len  ref  rows  Extra
      klientwojewodztwo ref kl_idx,woj_idx woj_idx 3 const 55054 Using where;
Using temporary; Using filesort
      klientbranza ref branzaid,klientid klientid 8 klientwojewodztwo.klientid 1
      branza ref id_na id_na 2 klientbranza.branzaid 1


but

EXPLAIN SELECT /*! SQL_BUFFER_RESULT */
STRAIGHT_JOIN DISTINCT branza.branzaid, branza.branzanazwa
FROM branza, klientbranza, klientwojewodztwo
WHERE branza.branzaid = klientbranza.branzaid AND klientbranza.klientid =
klientwojewodztwo.klientid AND wojewodztwoid =9
ORDER BY bsort ASC

shows me:

      table  type  possible_keys  key  key_len  ref  rows  Extra
      branza ALL id_na NULL NULL NULL 1451 Using temporary; Using filesort
      klientbranza ref branzaid,klientid branzaid 2 branza.branzaid 969 Distinct
      klientwojewodztwo ref kl_idx,woj_idx kl_idx 8 klientbranza.klientid 1
Using where; Distinct



how to understand that?

what should be the correct order in where clause and what indexes should be
used?

for this moment I have such indexes:

table branza:
bsort primary
id_na(branzaid,branzanazwa)

table klientbranza:
branzaid
klientid

table klientwojewodztwo:
klientid
wojewodztwoid

all these indexes are just "index" type [not unique or full...]



how to correct interpret these explains and how to correct this?

with regards
DeRyl



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]