help with proper conditions and indexes needed
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]
Re: help with proper conditions and indexes needed
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]
Re: help with proper conditions and indexes needed
I'm really sorry wasting your time but... but - at first I read all materials I found [including this chapter you suggested] I can't correct interpret these explains I described - I've tried to change the order in where clause, making different indexes but see no changes and no correction so I think I don't understand that and I decided to write to the list with respect DeRyl - Original Message - From: [EMAIL PROTECTED] To: DeRyl [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 27, 2004 3:53 PM Subject: Re: help with proper conditions and indexes needed 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
