Ok cool. This should make it easier for you to visualise what's going on!
There's a bunch of other fields and stuff in some of these tables that aren't involved with this specific query.
The tags table is just a list of various classifications: DEALER, MANUFACTURER, DISTRIBUTOR, SHOW EVENT ORGANISER, WEBSITE PROVIDER
This query will only be run ocassionally by me, a few times per month, to export our mailing list data into CSV format to process in Excel. So it's not a mission critical query that will be hammered by users every day. But it's just strange, our DB is tiny compared to many MySQL installations! Our total database is 21MB including indexes.
contacts:
Code: Select all
CREATE TABLE `contacts` ( `contact_id` INT(11) NOT NULL AUTO_INCREMENT, `company_id` INT(11) DEFAULT NULL, `address1_contact` VARCHAR(255) DEFAULT NULL, `address2_contact` VARCHAR(255) DEFAULT NULL, `address3_contact` VARCHAR(255) DEFAULT NULL, `business_card` VARCHAR(2) DEFAULT NULL, `contact_id_old` INT(20) DEFAULT NULL, `contact_name` VARCHAR(255) DEFAULT NULL, `county_state_contact` VARCHAR(100) DEFAULT NULL, `create_name` VARCHAR(60) DEFAULT NULL, `create_stamp` INT(10) DEFAULT NULL, `email_contact` VARCHAR(255) DEFAULT NULL, `email_contact_optout` TINYINT(1) DEFAULT NULL, `fax_contact` VARCHAR(255) DEFAULT NULL, `geo_contact` VARCHAR(255) DEFAULT NULL, `job_title` VARCHAR(255) DEFAULT NULL, `mail_contact` TINYINT(1) DEFAULT NULL, `modify_count` INT(7) DEFAULT '0', `modify_name` VARCHAR(60) DEFAULT NULL, `modify_stamp` INT(10) DEFAULT NULL, `note` TEXT, `postcode_zip_contact` VARCHAR(50) DEFAULT NULL, `reg_stamp` INT(10) DEFAULT NULL, `subscribed` TINYINT(1) DEFAULT NULL, `tel_cell` VARCHAR(255) DEFAULT NULL, `tel_contact` VARCHAR(255) DEFAULT NULL, `tel_ext` VARCHAR(255) DEFAULT NULL, `tel_home` VARCHAR(255) DEFAULT NULL, `temp` VARCHAR(50) DEFAULT NULL, `town_city_contact` VARCHAR(100) DEFAULT NULL, `view_count` INT(7) DEFAULT '0', `view_name` VARCHAR(60) DEFAULT NULL, `view_stamp` INT(10) DEFAULT NULL, PRIMARY KEY (`contact_id`), KEY `company_id` (`company_id`), KEY `contact_name` (`contact_name`)) ENGINE=MyISAM AUTO_INCREMENT=37645 DEFAULT CHARSET=utf8
companies:
Code: Select all
CREATE TABLE `companies` ( `company_id` INT(11) NOT NULL AUTO_INCREMENT, `address_priv` TINYINT(1) DEFAULT NULL, `address1` VARCHAR(255) DEFAULT NULL, `address2` VARCHAR(255) DEFAULT NULL, `address3` VARCHAR(255) DEFAULT NULL, `advisory` VARCHAR(255) DEFAULT NULL, `advisory_name` VARCHAR(60) DEFAULT NULL, `advisory_stamp` INT(10) DEFAULT NULL, `agency_address` VARCHAR(255) DEFAULT NULL, `agency_company` VARCHAR(255) DEFAULT NULL, `agency_contact` VARCHAR(255) DEFAULT NULL, `agency_email` VARCHAR(255) DEFAULT NULL, `agency_fax` VARCHAR(255) DEFAULT NULL, `agency_id` INT(11) DEFAULT NULL, `agency_tel` VARCHAR(255) DEFAULT NULL, `budget_year1` VARCHAR(3) DEFAULT NULL, `budget_year2` VARCHAR(3) DEFAULT NULL, `company_name` TEXT, `county_state` VARCHAR(100) DEFAULT NULL, `create_name` VARCHAR(60) DEFAULT NULL, `create_stamp` INT(10) DEFAULT NULL, `currency` VARCHAR(3) DEFAULT NULL, `email` TEXT, `email_optout` TINYINT(1) DEFAULT NULL, `email_priv` TINYINT(1) DEFAULT NULL, `fax` VARCHAR(255) DEFAULT NULL, `fax_priv` TINYINT(1) DEFAULT NULL, `geo` VARCHAR(2) DEFAULT NULL, `hanging_file` TINYINT(1) DEFAULT NULL, `iso_code` VARCHAR(20) DEFAULT NULL, `locked_name` VARCHAR(60) DEFAULT NULL, `locked_stamp` INT(10) DEFAULT NULL, `master_id` INT(11) DEFAULT NULL, `modify_count` INT(7) DEFAULT '0', `modify_name` VARCHAR(60) DEFAULT NULL, `modify_stamp` INT(10) DEFAULT NULL, `postcode_zip` VARCHAR(50) DEFAULT NULL, `rep` VARCHAR(10) DEFAULT NULL, `tel` VARCHAR(255) DEFAULT NULL, `tel_priv` TINYINT(1) DEFAULT NULL, `town_city` VARCHAR(255) DEFAULT NULL, `vat_number` VARCHAR(20) DEFAULT NULL, `view_count` INT(7) DEFAULT '0', `view_name` VARCHAR(60) DEFAULT NULL, `view_stamp` INT(10) DEFAULT NULL, `website` TEXT, PRIMARY KEY (`company_id`), KEY `geo` (`geo`), FULLTEXT KEY `company_name` (`company_name`)) ENGINE=MyISAM AUTO_INCREMENT=39200 DEFAULT CHARSET=utf8
tags_data:
Code: Select all
CREATE TABLE `tags_data` ( `tagdata_id` INT(11) NOT NULL AUTO_INCREMENT, `tag_id` INT(11) DEFAULT NULL, `company_id` VARCHAR(6) DEFAULT NULL, `create_name` VARCHAR(60) DEFAULT NULL, `create_stamp` INT(10) DEFAULT NULL, PRIMARY KEY (`tagdata_id`), KEY `tag_id` (`tag_id`), KEY `company_id` (`company_id`), KEY `companies_tags` (`company_id`,`tag_id`)) ENGINE=MyISAM AUTO_INCREMENT=42205 DEFAULT CHARSET=utf8
tags:
Code: Select all
CREATE TABLE `tags` ( `tag_id` INT(7) NOT NULL DEFAULT '0', `category` VARCHAR(255) DEFAULT NULL, `description` VARCHAR(255) DEFAULT NULL, `name` VARCHAR(255) DEFAULT NULL, PRIMARY KEY (`tag_id`), KEY `category` (`category`), KEY `name` (`name`)) ENGINE=MyISAM DEFAULT CHARSET=utf8
Thanks so much!
