RE: Query Not Using Indexing
Hi! > "Sinisa" == Sinisa Milivojevic <[EMAIL PROTECTED]> writes: Sinisa> Adam Douglas writes: >> Another thing that happens is when I tried to ALTER PostalCodeInfo and >> PostalCodeReps table schemas MySQL seemed to be hung and put the CPU usage >> at 99.0% to 99.02% (never ending it seems). Both those tables only have >> 42657 rows each. Any ideas why these two tables seem to hang when anything >> major is done on them? Funny thing is it seems to be random when you execute >> a query that uses these two tables. Is there something I'm missing for MySQL >> configuration or bad table design? I'll post the table schema's below the >> explain query. Sinisa> Regarding ALTER TABLE, that is also expected behaviour, as MySQL is Sinisa> re-building indices and has to use lot's of CPU. Of course the ALTER TABLE should not take forever. How long did you try this ? If MySQL really would hang doing this, we would really like to take a look a this to find out what's wrong! Please check that you don't get 'disk full' when doing the ALTER TABLE. If you are sure this is a bug, please ftp the tables to ftp://support.mysql.com/pub/mysql/secret together with a description of how to repeat the problem. Regards, Monty PS: I assume you are using MySQL 3.23.40; If not, try to upgrade before submitting the files to us! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Query Not Using Indexing
Hi! > "Sinisa" == Sinisa Milivojevic <[EMAIL PROTECTED]> writes: Sinisa> Adam Douglas writes: Sinisa> >> >> Alright, but is there no way then to decrease my query time of 14 seconds >> (includes 3 queries) to something more reasonable for use on-line? 10 >> seconds according to stats is the maximum limit. >> >> Maybe I'm not understanding things correctly here, but I do not have a >> Cartesian product since my query will only return 1 result. Cartesian is >> when MySQL has to match row for row when tables do not have any relations >> correct (in brief)? >> >> Well that doesn't make sense to me, so how long should such a task take >> then? I've ran it all night and still it wasn't finished, seems to be hung >> not just taking to long. When you have a table with 42,000+ rows it should >> take more then 8 hours to update no? >> Sinisa> If it returns one row only, then I was wrong, it is not Cartesion Sinisa> product. Sinisa> But your query is neither 1 -> N -> N join, that is resolved Sinisa> faster. Sinisa> As I told you before you do miss some indices. Sinisa> You can also speed up things by using /* STRAIGHT_JOIN */ where possible. Adam, could you email us the output of SHOW CREATE TABLE for all your tables involved in the query together with an EXPLAIN SELECT for the query in question. This should help us understand better what is going on. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Query Not Using Indexing
Adam Douglas writes: > Ahh what do you mean "all tables are not related with common columns"? They > are not suppose to be related with a common columns. PostalCodeInfo and > Company table are completely two different things. > > Yes that is correct, I've resolved the issue with PostalCodeInfo for > indexing in the query. I had column types not matching the same through my > tables. But I'm still lost as to why I can not get CompanyShipInfo to use > indexing in the query. > > That's find but shouldn't I still be able to go down even further as to how > many rows are analyzed? CompanyShipInfo has 235 rows and all are being read > by the query. Here's the new explain of the query below. > > Another thing that happens is when I tried to ALTER PostalCodeInfo and > PostalCodeReps table schemas MySQL seemed to be hung and put the CPU usage > at 99.0% to 99.02% (never ending it seems). Both those tables only have > 42657 rows each. Any ideas why these two tables seem to hang when anything > major is done on them? Funny thing is it seems to be random when you execute > a query that uses these two tables. Is there something I'm missing for MySQL > configuration or bad table design? I'll post the table schema's below the > explain query. MySQL is truly doing it's best in your case. If of 6 tables you have a broken chain of relations, you will have a Cartesian product. Regarding ALTER TABLE, that is also expected behaviour, as MySQL is re-building indices and has to use lot's of CPU. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Not Using Indexing
Hi! > "Sinisa" == Sinisa Milivojevic <[EMAIL PROTECTED]> writes: Sinisa> Adam Douglas writes: >> I'm running MySQL v3.22.32. Here's my problem I run the following >> select query and both CompanyRep and PostalCodeInfo tables are not >> using my Indexing. Why? How can I get these two tables to use indexing >> to speed up my query. You can look at my indexing on the two tables >> below the query. >> >> Any help would be greatly appreciated, thanks! >> >> SELECT >> Company.Company_Name, >> CompanyShipInfo.Ship_Addr1, >> CompanyShipInfo.Ship_Addr2, >> CompanyShipInfo.Ship_City, >> ProvincesStates.Prov_State_Name, >> Countries.Country_Name, >> CompanyShipInfo.Ship_Postal_Code, >> Company.Phone, >> Company.Fax, >> Company.Email, >> Company.Web_Page >> FROM >> CompanyRep, Company, CompanyShipInfo, PostalCodeInfo, >> ProvincesStates, Countries >> WHERE >> ( >> CompanyShipInfo.Prov_State_ID=PostalCodeInfo.Prov_State_ID >> AND PostalCodeInfo.Postal_Code=98188 >> AND CompanyRep.Company_ID=Company.Company_ID >> AND CompanyShipInfo.Company_ID=Company.Company_ID >> AND CompanyShipInfo.Prov_State_ID=ProvincesStates.Prov_State_ID >> AND CompanyShipInfo.Country_ID=Countries.Country_ID >> AND Company.Company_Status_ID=1 >> AND CompanyRep.Invoice_Only=0 >> AND CompanyRep.Wholesale_Rep=-1 >> ) Sinisa> Hi! Sinisa> You have 6 tables in a join. Sinisa> Possible causes of not using indices could be some index missing or Sinisa> low number of rows. Sinisa> Beside that, you do have Cartesian product as there are some tables Sinisa> not related. Try running an EXPLAIN for the query to verify which indexes are used. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Query Not Using Indexing
> Adam Douglas writes: > > Correct 6 tables are being joined. Here's the explain of > the query below > > (btw, I'm not getting a index on CompanyShipInfo and > PostalCodeInfo not > > CompanyRep like I previous said). Excuse me if this seems > dumb but I'm not > > sure what you are saying in the last two comments. I > understand an index > > could be missing but where? I'll put the indexing/schema > below the explain > > for you. Basically I need to get the query to run faster. I > have three > > queries one for our Applied Product, Unitary Product and > Wholesalers. A user > > will send to the query a US Zip Code and based on that zip > code it will > > return the representative(s) that looks after that area > (according to zip > > code) for each product line. It takes about 14 seconds to > run this query and > > two others via PHP. Now this seems to vary and sometimes > renders mysqld > > useless. Mysqld will not accept at times user logins or > even checking for > > database status. The CPU usage on this process when it > doesn't work goes > > from 99.0% to 99.02%. It doesn't seem to matter how long > you leave it, never > > seems to end. Now the biggest tables I have is PostalCodeInfo and > > PostalCodeReps. Both contain about 42657 rows, so table > size is not a > > problem. > > > > If you scrutinize more closely your query and your table you can > notice that : > > - all tables are not related with common columns > > - There are two indices missing > > - MySQL is using all of the indices available except on some small > tables, like CompanyShipInfo Ahh what do you mean "all tables are not related with common columns"? They are not suppose to be related with a common columns. PostalCodeInfo and Company table are completely two different things. Yes that is correct, I've resolved the issue with PostalCodeInfo for indexing in the query. I had column types not matching the same through my tables. But I'm still lost as to why I can not get CompanyShipInfo to use indexing in the query. That's find but shouldn't I still be able to go down even further as to how many rows are analyzed? CompanyShipInfo has 235 rows and all are being read by the query. Here's the new explain of the query below. Another thing that happens is when I tried to ALTER PostalCodeInfo and PostalCodeReps table schemas MySQL seemed to be hung and put the CPU usage at 99.0% to 99.02% (never ending it seems). Both those tables only have 42657 rows each. Any ideas why these two tables seem to hang when anything major is done on them? Funny thing is it seems to be random when you execute a query that uses these two tables. Is there something I'm missing for MySQL configuration or bad table design? I'll post the table schema's below the explain query. Explain of Query +-++---+ ---+-+---+--++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-++---+ ---+-+---+--++ | CompanyShipInfo | ALL| Company_ID,Prov_State_ID,Country_ID | NULL |NULL | NULL | 345 || | Company | eq_ref | PRIMARY,Company_Status_ID | PRIMARY | 3 | CompanyShipInfo.Company_ID|1 | where used | | CompanyRep | ref| Company_ID,Wholesale_Rep,Invoice_Only | Company_ID| 3 | Company.Company_ID|1 | where used | | PostalCodeInfo | ref| Prov_State_ID,Postal_Code | Prov_State_ID | 2 | CompanyShipInfo.Prov_State_ID | 223 | where used | | ProvincesStates | eq_ref | PRIMARY | PRIMARY | 2 | CompanyShipInfo.Prov_State_ID |1 || | Countries | eq_ref | PRIMARY | PRIMARY | 2 | CompanyShipInfo.Country_ID|1 || +-++---+ ---+-+---+--++ 6 rows in set (0.00 sec) PostalCodeInfo Schema +-+--+--+-+-+--- -+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+--- -+ | Postal_Code_Info_ID | mediumint(9) | | PRI | 0 | auto_increment | | City| varchar(50) | YES | | NULL| | | Prov_State_ID | smallint(4) | | MUL | 0 | | | Country_ID | smallint(3) | | MUL | 0 | | | Postal_Code | varchar(10) | | MUL | | | | County | varchar(50) | YES | | NULL| | +-+--+--+-+-+--
RE: Query Not Using Indexing
Adam Douglas writes: > Correct 6 tables are being joined. Here's the explain of the query below > (btw, I'm not getting a index on CompanyShipInfo and PostalCodeInfo not > CompanyRep like I previous said). Excuse me if this seems dumb but I'm not > sure what you are saying in the last two comments. I understand an index > could be missing but where? I'll put the indexing/schema below the explain > for you. Basically I need to get the query to run faster. I have three > queries one for our Applied Product, Unitary Product and Wholesalers. A user > will send to the query a US Zip Code and based on that zip code it will > return the representative(s) that looks after that area (according to zip > code) for each product line. It takes about 14 seconds to run this query and > two others via PHP. Now this seems to vary and sometimes renders mysqld > useless. Mysqld will not accept at times user logins or even checking for > database status. The CPU usage on this process when it doesn't work goes > from 99.0% to 99.02%. It doesn't seem to matter how long you leave it, never > seems to end. Now the biggest tables I have is PostalCodeInfo and > PostalCodeReps. Both contain about 42657 rows, so table size is not a > problem. > If you scrutinize more closely your query and your table you can notice that : - all tables are not related with common columns - There are two indices missing - MySQL is using all of the indices available except on some small tables, like CompanyShipInfo -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Query Not Using Indexing
> -Original Message- > From: Sinisa Milivojevic [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, July 31, 2001 1:27 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: Query Not Using Indexing > > > Adam Douglas writes: > > I'm running MySQL v3.22.32. Here's my problem I run the following > > select query and both CompanyRep and PostalCodeInfo tables are not > > using my Indexing. Why? How can I get these two tables to > use indexing > > to speed up my query. You can look at my indexing on the two tables > > below the query. > > > > Any help would be greatly appreciated, thanks! > > > > SELECT > > Company.Company_Name, > > CompanyShipInfo.Ship_Addr1, > > CompanyShipInfo.Ship_Addr2, > > CompanyShipInfo.Ship_City, > > ProvincesStates.Prov_State_Name, > > Countries.Country_Name, > > CompanyShipInfo.Ship_Postal_Code, > > Company.Phone, > > Company.Fax, > > Company.Email, > > Company.Web_Page > > FROM > > CompanyRep, Company, CompanyShipInfo, PostalCodeInfo, > > ProvincesStates, Countries > > WHERE > > ( > > CompanyShipInfo.Prov_State_ID=PostalCodeInfo.Prov_State_ID > > AND PostalCodeInfo.Postal_Code=98188 > > AND CompanyRep.Company_ID=Company.Company_ID > > AND CompanyShipInfo.Company_ID=Company.Company_ID > > AND CompanyShipInfo.Prov_State_ID=ProvincesStates.Prov_State_ID > > AND CompanyShipInfo.Country_ID=Countries.Country_ID > > AND Company.Company_Status_ID=1 > > AND CompanyRep.Invoice_Only=0 > > AND CompanyRep.Wholesale_Rep=-1 > > ) > > Hi! > > You have 6 tables in a join. > > Possible causes of not using indices could be some index missing or > low number of rows. > > Beside that, you do have Cartesian product as there are some tables > not related. Correct 6 tables are being joined. Here's the explain of the query below (btw, I'm not getting a index on CompanyShipInfo and PostalCodeInfo not CompanyRep like I previous said). Excuse me if this seems dumb but I'm not sure what you are saying in the last two comments. I understand an index could be missing but where? I'll put the indexing/schema below the explain for you. Basically I need to get the query to run faster. I have three queries one for our Applied Product, Unitary Product and Wholesalers. A user will send to the query a US Zip Code and based on that zip code it will return the representative(s) that looks after that area (according to zip code) for each product line. It takes about 14 seconds to run this query and two others via PHP. Now this seems to vary and sometimes renders mysqld useless. Mysqld will not accept at times user logins or even checking for database status. The CPU usage on this process when it doesn't work goes from 99.0% to 99.02%. It doesn't seem to matter how long you leave it, never seems to end. Now the biggest tables I have is PostalCodeInfo and PostalCodeReps. Both contain about 42657 rows, so table size is not a problem. EXPLAIN Query +-++---+ +-+---+---+- -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-++---+ +-+---+---+- -+ | CompanyShipInfo | ALL| Company_ID| NULL |NULL | NULL | 345 | | | Company | eq_ref | PRIMARY,Company_Status_ID | PRIMARY | 3 | CompanyShipInfo.Company_ID| 1 | where used | | CompanyRep | ref| Company_ID,Wholesale_Rep,Invoice_Only | Company_ID | 3 | Company.Company_ID| 1 | where used | | PostalCodeInfo | ALL| Prov_State_ID | NULL |NULL | NULL | 42657 | range checked for each record (index map: 2) | | ProvincesStates | eq_ref | PRIMARY | PRIMARY | 2 | CompanyShipInfo.Prov_State_ID | 1 | | | Countries | eq_ref | PRIMARY | PRIMARY | 2 | CompanyShipInfo.Country_ID| 1 | | +-++---+ +-+---+---+- -+ 6 rows in set (0.00 sec) CompanyShipInfo Table Schema +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+-
Re: Query Not Using Indexing
Adam Douglas writes: > I'm running MySQL v3.22.32. Here's my problem I run the following > select query and both CompanyRep and PostalCodeInfo tables are not > using my Indexing. Why? How can I get these two tables to use indexing > to speed up my query. You can look at my indexing on the two tables > below the query. > > Any help would be greatly appreciated, thanks! > > SELECT > Company.Company_Name, > CompanyShipInfo.Ship_Addr1, > CompanyShipInfo.Ship_Addr2, > CompanyShipInfo.Ship_City, > ProvincesStates.Prov_State_Name, > Countries.Country_Name, > CompanyShipInfo.Ship_Postal_Code, > Company.Phone, > Company.Fax, > Company.Email, > Company.Web_Page > FROM > CompanyRep, Company, CompanyShipInfo, PostalCodeInfo, > ProvincesStates, Countries > WHERE > ( > CompanyShipInfo.Prov_State_ID=PostalCodeInfo.Prov_State_ID > AND PostalCodeInfo.Postal_Code=98188 > AND CompanyRep.Company_ID=Company.Company_ID > AND CompanyShipInfo.Company_ID=Company.Company_ID > AND CompanyShipInfo.Prov_State_ID=ProvincesStates.Prov_State_ID > AND CompanyShipInfo.Country_ID=Countries.Country_ID > AND Company.Company_Status_ID=1 > AND CompanyRep.Invoice_Only=0 > AND CompanyRep.Wholesale_Rep=-1 > ) > > mysql> show index from CompanyRep; > +++---+--+---+-- > -+-+--+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name > | Collation | Cardinality | Sub_part | > +++---+--+---+-- > -+-+--+ > | CompanyRep | 0 | PRIMARY |1 | > CompanyRep_ID | A | 213 | NULL | > | CompanyRep | 1 | Company_ID|1 | Company_ID > | A |NULL | NULL | > | CompanyRep | 1 | Wholesale_Rep |1 | > Wholesale_Rep | A |NULL | NULL | > | CompanyRep | 1 | Invoice_Only |1 | > Invoice_Only | A |NULL | NULL | > +++---+--+---+-- > -+-+--+ > > mysql> show index from PostalCodeInfo; > +++---+--+-- > ---+---+-+--+ > | Table | Non_unique | Key_name | Seq_in_index | > Column_name | Collation | Cardinality | Sub_part | > +++---+--+-- > ---+---+-+--+ > | PostalCodeInfo | 0 | PRIMARY |1 | > Postal_Code_Info_ID | A | 42657 | NULL | > | PostalCodeInfo | 1 | Prov_State_ID |1 | > Prov_State_ID | A |NULL | NULL | > | PostalCodeInfo | 1 | Country_ID|1 | > Country_ID | A |NULL | NULL | > | PostalCodeInfo | 1 | Postal_Code |1 | > Postal_Code | A |NULL | NULL | > +++---+--+-- > ---+---+-+--+ > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Not Using Indexing
Adam Douglas writes: > I'm running MySQL v3.22.32. Here's my problem I run the following > select query and both CompanyRep and PostalCodeInfo tables are not > using my Indexing. Why? How can I get these two tables to use indexing > to speed up my query. You can look at my indexing on the two tables > below the query. > > Any help would be greatly appreciated, thanks! > > SELECT > Company.Company_Name, > CompanyShipInfo.Ship_Addr1, > CompanyShipInfo.Ship_Addr2, > CompanyShipInfo.Ship_City, > ProvincesStates.Prov_State_Name, > Countries.Country_Name, > CompanyShipInfo.Ship_Postal_Code, > Company.Phone, > Company.Fax, > Company.Email, > Company.Web_Page > FROM > CompanyRep, Company, CompanyShipInfo, PostalCodeInfo, > ProvincesStates, Countries > WHERE > ( > CompanyShipInfo.Prov_State_ID=PostalCodeInfo.Prov_State_ID > AND PostalCodeInfo.Postal_Code=98188 > AND CompanyRep.Company_ID=Company.Company_ID > AND CompanyShipInfo.Company_ID=Company.Company_ID > AND CompanyShipInfo.Prov_State_ID=ProvincesStates.Prov_State_ID > AND CompanyShipInfo.Country_ID=Countries.Country_ID > AND Company.Company_Status_ID=1 > AND CompanyRep.Invoice_Only=0 > AND CompanyRep.Wholesale_Rep=-1 > ) Hi! You have 6 tables in a join. Possible causes of not using indices could be some index missing or low number of rows. Beside that, you do have Cartesian product as there are some tables not related. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Not Using Indexing
I'm running MySQL v3.22.32. Here's my problem I run the following select query and both CompanyRep and PostalCodeInfo tables are not using my Indexing. Why? How can I get these two tables to use indexing to speed up my query. You can look at my indexing on the two tables below the query. Any help would be greatly appreciated, thanks! SELECT Company.Company_Name, CompanyShipInfo.Ship_Addr1, CompanyShipInfo.Ship_Addr2, CompanyShipInfo.Ship_City, ProvincesStates.Prov_State_Name, Countries.Country_Name, CompanyShipInfo.Ship_Postal_Code, Company.Phone, Company.Fax, Company.Email, Company.Web_Page FROM CompanyRep, Company, CompanyShipInfo, PostalCodeInfo, ProvincesStates, Countries WHERE ( CompanyShipInfo.Prov_State_ID=PostalCodeInfo.Prov_State_ID AND PostalCodeInfo.Postal_Code=98188 AND CompanyRep.Company_ID=Company.Company_ID AND CompanyShipInfo.Company_ID=Company.Company_ID AND CompanyShipInfo.Prov_State_ID=ProvincesStates.Prov_State_ID AND CompanyShipInfo.Country_ID=Countries.Country_ID AND Company.Company_Status_ID=1 AND CompanyRep.Invoice_Only=0 AND CompanyRep.Wholesale_Rep=-1 ) mysql> show index from CompanyRep; +++---+--+---+-- -+-+--+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | +++---+--+---+-- -+-+--+ | CompanyRep | 0 | PRIMARY |1 | CompanyRep_ID | A | 213 | NULL | | CompanyRep | 1 | Company_ID|1 | Company_ID | A |NULL | NULL | | CompanyRep | 1 | Wholesale_Rep |1 | Wholesale_Rep | A |NULL | NULL | | CompanyRep | 1 | Invoice_Only |1 | Invoice_Only | A |NULL | NULL | +++---+--+---+-- -+-+--+ mysql> show index from PostalCodeInfo; +++---+--+-- ---+---+-+--+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | +++---+--+-- ---+---+-+--+ | PostalCodeInfo | 0 | PRIMARY |1 | Postal_Code_Info_ID | A | 42657 | NULL | | PostalCodeInfo | 1 | Prov_State_ID |1 | Prov_State_ID | A |NULL | NULL | | PostalCodeInfo | 1 | Country_ID|1 | Country_ID | A |NULL | NULL | | PostalCodeInfo | 1 | Postal_Code |1 | Postal_Code | A |NULL | NULL | +++---+--+-- ---+---+-+--+ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
