Re: Optimizing query

[Marko Kupusic]

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