I will be using zip-5. Even after creating an index on the zip, mysql doesn't use it and doesn't even see it as a possible key. This is the show create table:
CREATE TABLE `links` (
`link_id` mediumint(8) NOT NULL auto_increment,
`link_name` varchar(150) default NULL,
`telephone` varchar(11) default NULL,
`email` varchar(50) default NULL,
`website` varchar(50) default NULL,
`address` varchar(150) default NULL,
`city` varchar(50) default NULL,
`state` varchar(50) default NULL,
`postcode` varchar(5) NOT NULL,
`alias` varchar(255) NOT NULL,
`link_desc` mediumtext NOT NULL,
`user_id` int(11) NOT NULL default '0',
`link_hits` int(11) NOT NULL default '0',
`link_votes` int(11) NOT NULL default '0',
`link_rating` decimal(7,6) unsigned NOT NULL default '0.000000',
`link_featured` smallint(6) NOT NULL default '0',
`link_published` tinyint(4) NOT NULL default '1',
`link_approved` int(4) NOT NULL default '1',
`link_template` varchar(255) NOT NULL,
`attribs` text NOT NULL,
`metakey` text NOT NULL,
`metadesc` text NOT NULL,
`internal_notes` text NOT NULL,
`ordering` int(11) NOT NULL default '0',
`link_created` datetime NOT NULL,
`publish_up` datetime NOT NULL,
`publish_down` datetime NOT NULL,
`link_modified` datetime NOT NULL,
`link_visited` int(11) NOT NULL default '0',
`fax` varchar(255) NOT NULL,
`lat` float NOT NULL,
`lng` float NOT NULL,
`zoom` tinyint(3) unsigned NOT NULL,
`country` varchar(40) NOT NULL default 'USA',
PRIMARY KEY (`link_id`),
KEY `publishing` (`link_published`,`link_approved`,`publish_up`,`publish_down`),
KEY `mylisting` (`user_id`,`link_id`),
KEY `count_listfeatured` (`link_published`,`link_approved`,`link_featured`,`publish_up`,`publish_down`,`link_id`),
KEY `link_name` (`link_name`),
KEY `link_votes` (`link_votes`),
KEY `link_rating` (`link_rating`),
KEY `postcode` (`postcode`)
) ENGINE=MyISAM AUTO_INCREMENT=2962 DEFAULT CHARSET=utf8
and here are results from an explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE l ref PRIMARY,publishing,count_listfeatured count_listfeatured 5 const,const 2805 Using where
1 SIMPLE img ref link_id_ordering link_id_ordering 8 myc0923408475715.l.link_id,const 1
1 SIMPLE cl ref link_id link_id 4 myc0923408475715.l.link_id 1 Using where
1 SIMPLE c eq_ref PRIMARY,cat_id PRIMARY 4 myc0923408475715.cl.cat_id 1
1 SIMPLE u eq_ref PRIMARY PRIMARY 4 myc0923408475715.l.user_id 1
phpmyadmin is saying my queries are taking about .01 to .02 seconds but this is only with 3000 rows. I wonder what this will be like with millions?