I really hate to do this to you but if I tried to answer your questions I would be copying from the book anyway. There is a section in the manual that deals specifically with query optimization. It covers index creation and usage, when an order by will and won't use and index and a bunch of other topics. Please review this material and come back to us if you need help understanding anything there. We will all happily do our best to make sense of whatever you are still having problems with.
http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine "DeRyl" <[EMAIL PROTECTED]> wrote on 10/27/2004 09:31:58 AM: > 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] >
