MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimizing query
Posted by: Marko Kupusic
Date: February 27, 2012 05:57AM

I hope this is what you wanted. All those arg in the table are NULL. I since removed some entries from the database to make it a little smaller, but it still not going through. Id is just auto incremented and is not really of use in this.

explain on the query
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY country_info ALL status NULL NULL NULL 676239 Using where
2 DEPENDENT SUBQUERY country_info range status status 103 NULL 192420 Using where

show indexes from country_info
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
country_info 0 PRIMARY 1 id A 676239 NULL NULL BTREE
country_info 1 status 1 status A 7 NULL NULL YES BTREE
country_info 1 zip 1 zip A 142119 NULL NULL YES BTREE


show create table country_info
Table Create Table
country_info CREATE TABLE `country_info` (
`status` varchar(100) collate latin1_general_ci default NULL,
`name` varchar(100) collate latin1_general_ci default NULL,
`surname` varchar(100) collate latin1_general_ci default NULL,
`userid` varchar(100) collate latin1_general_ci default NULL,
`city` varchar(100) collate latin1_general_ci default NULL,
`zip` varchar(100) collate latin1_general_ci default NULL,
`phone` varchar(100) collate latin1_general_ci default NULL,
`family members` varchar(100) collate latin1_general_ci default NULL,
`job` varchar(100) collate latin1_general_ci default NULL,
`arg1` varchar(100) collate latin1_general_ci default NULL,
`arg2` varchar(100) collate latin1_general_ci default NULL,
`arg3` varchar(100) collate latin1_general_ci default NULL,
`arg4` varchar(100) collate latin1_general_ci default NULL,
`arg5` varchar(100) collate latin1_general_ci default NULL,
`arg6` varchar(100) collate latin1_general_ci default NULL,
`arg7` varchar(100) collate latin1_general_ci default NULL,
`arg8` varchar(100) collate latin1_general_ci default NULL,
`arg9` varchar(100) collate latin1_general_ci default NULL,
`arg10` varchar(100) collate latin1_general_ci default NULL,
`arg11` varchar(100) collate latin1_general_ci default NULL,
`arg12` varchar(100) collate latin1_general_ci default NULL,
`arg13` varchar(100) collate latin1_general_ci default NULL,
`arg14` varchar(100) collate latin1_general_ci default NULL,
`arg15` varchar(100) collate latin1_general_ci default NULL,
`arg16` varchar(100) collate latin1_general_ci default NULL,
`arg17` varchar(100) collate latin1_general_ci default NULL,
`arg18` varchar(100) collate latin1_general_ci default NULL,
`arg19` varchar(100) collate latin1_general_ci default NULL,
`arg20` varchar(100) collate latin1_general_ci default NULL,
`arg21` varchar(100) collate latin1_general_ci default NULL,
`arg22` varchar(100) collate latin1_general_ci default NULL,
`arg23` varchar(100) collate latin1_general_ci default NULL,
`arg24` varchar(100) collate latin1_general_ci default NULL,
`arg25` varchar(100) collate latin1_general_ci default NULL,
`arg26` varchar(100) collate latin1_general_ci default NULL,
`arg27` varchar(100) collate latin1_general_ci default NULL,
`arg28` varchar(100) collate latin1_general_ci default NULL,
`arg29` varchar(100) collate latin1_general_ci default NULL,
`arg30` varchar(100) collate latin1_general_ci default NULL,
`arg31` varchar(100) collate latin1_general_ci default NULL,
`id` int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`),
KEY `status` (`status`),
KEY `zip` (`zip`)
) ENGINE=MyISAM AUTO_INCREMENT=676240 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Options: ReplyQuote


Subject
Views
Written By
Posted
1713
February 24, 2012 06:07AM
963
February 24, 2012 01:16PM
851
February 25, 2012 03:49AM
Re: Optimizing query
1133
February 27, 2012 05:57AM
903
February 25, 2012 05:54PM
833
February 27, 2012 06:04AM
910
February 28, 2012 07:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.