Re: Seemingly Simple Query Takes Much Too Long
I revised the structure and added indexes on the valid field, and was able to bring the query time down, but still think that it should be quicker.
I dont have any output in slow query that I can find.
Each of the three tables has a primary-key made up of both the `uid` and `serv_id` fields, due to replication. Why are these primary keys useless? I reversed the primary key definitions so that `serv_id` appears before `uid`, but there was no change in execute time.
Here is the 'show create' for the three tables:
CREATE TABLE `vendor` (
`serv_id` char(1) NOT NULL default 'a',
`uid` smallint(5) unsigned NOT NULL auto_increment,
`company_name` varchar(50) NOT NULL default '',
`company_url` varchar(100) default NULL,
`support_url` varchar(100) default NULL,
`support_user` varchar(30) default NULL,
`support_pass` varchar(20) default NULL,
`comments` varchar(255) default NULL,
`valid` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`uid`,`serv_id`),
UNIQUE KEY `company_name` (`company_name`),
KEY `valid` (`valid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `product` (
`serv_id` char(1) NOT NULL default 'a',
`uid` mediumint(8) unsigned NOT NULL auto_increment,
`vendor_id` varchar(7) NOT NULL default '',
`software_name` varchar(100) NOT NULL default '',
`export_regulations` char(1) default '',
`software_function` varchar(25) default NULL,
`software_url` varchar(100) default NULL,
`comments` varchar(255) default NULL,
`valid` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`uid`,`serv_id`),
UNIQUE KEY `vendor_id` (`vendor_id`,`software_name`),
KEY `valid` (`valid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `version` (
`serv_id` char(1) NOT NULL default 'a',
`uid` mediumint(8) unsigned NOT NULL auto_increment,
`product_id` varchar(10) NOT NULL default '',
`version_name` varchar(10) NOT NULL default '',
`phys_media_asset` varchar(50) default '',
`location_of_media` varchar(50) default '',
`part_number` varchar(20) default '',
`cmvc_archive_loc` varchar(100) default '',
`comments` varchar(255) default NULL,
`valid` tinyint(1) NOT NULL default '1',
PRIMARY KEY (`uid`,`serv_id`),
UNIQUE KEY `product_id` (`product_id`,`version_name`),
KEY `valid` (`valid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1