rendered paste bodyOn 5.0.45:
mysql> SELECT app.id,leads_notes.note,CONCAT(cnt.title,' ',cnt.firstname,' ',cnt.surname) AS fullname,users.sales_name,app.occupants,app.size,(SELECT CONCAT(appfd.date,' ',appfd.time) AS last_contact_date FROM applicant_feedback appfd WHERE appfd.applicant_id=app.id ORDER BY appfd.id DESC LIMIT 1) AS last_contact_date,DATE_FORMAT(app.manager_reminder_time,' %d/%m/%Y %H:%i:%s') AS manager_reminder_time FROM applicant app RIGHT JOIN users ON users.user_id=app.logged_by RIGHT JOIN contact cnt ON cnt.parent_id=app.id AND cnt.type='APPLICANT' AND cnt.main_contact=1 LEFT JOIN leads_notes ON leads_notes.applicant_id=app.id WHERE app.lead_status_last='hot_big' AND app.status_id <> 2 AND app.status_id <> 0 AND app.manager_reminder='1' AND app.deleted='0' AND manager_reminder_time < '2012-02-09' ORDER by app.manager_reminder_time DESC;
295 rows in set (0.35 sec)
mysql> EXPLAIN SELECT app.id,leads_notes.note,CONCAT(cnt.title,' ',cnt.firstname,' ',cnt.surname) AS fullname,users.sales_name,app.occupants,app.size,(SELECT CONCAT(appfd.date,' ',appfd.time) AS last_contact_date FROM applicant_feedback appfd WHERE appfd.applicant_id=app.id ORDER BY appfd.id DESC LIMIT 1) AS last_contact_date,DATE_FORMAT(app.manager_reminder_time,' %d/%m/%Y %H:%i:%s') AS manager_reminder_time FROM applicant app RIGHT JOIN users ON users.user_id=app.logged_by RIGHT JOIN contact cnt ON cnt.parent_id=app.id AND cnt.type='APPLICANT' AND cnt.main_contact=1 LEFT JOIN leads_notes ON leads_notes.applicant_id=app.id WHERE app.lead_status_last='hot_big' AND app.status_id <> 2 AND app.status_id <> 0 AND app.manager_reminder='1' AND app.deleted='0' AND manager_reminder_time < '2012-02-09' ORDER by app.manager_reminder_time DESC \G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: cnt
type: ALL
possible_keys: parent_id
key: NULL
key_len: NULL
ref: NULL
rows: 54917
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: app
type: eq_ref
possible_keys: PRIMARY,logged_by,deleted
key: PRIMARY
key_len: 3
ref: officeapp.cnt.parent_id
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: users
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: officeapp.app.logged_by
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: leads_notes
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: officeapp.app.id
rows: 1
Extra:
*************************** 5. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: appfd
type: ref
possible_keys: applicant_id
key: applicant_id
key_len: 3
ref: officeapp.app.id
rows: 9
Extra: Using where; Using filesort
5 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE applicant \G;
*************************** 1. row ***************************
Table: applicant
Create Table: CREATE TABLE `applicant` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`original_id` int(10) unsigned NOT NULL default '0',
`external_id` varchar(10) NOT NULL default '',
`logged_by` mediumint(8) default '2',
`logged_date` datetime NOT NULL default '0000-00-00 00:00:00',
`company_name` varchar(120) NOT NULL default '',
`trading_name` varchar(200) NOT NULL,
`trading_number` varchar(200) NOT NULL,
`business_type` varchar(100) NOT NULL default '',
`address1` varchar(100) NOT NULL default '',
`address2` varchar(100) NOT NULL default '',
`address3` varchar(100) NOT NULL default '',
`postcode` varchar(100) NOT NULL default '',
`country` varchar(100) NOT NULL default '',
`tel1` varchar(100) NOT NULL default '',
`tel2` varchar(100) NOT NULL default '',
`tel3` varchar(100) NOT NULL default '',
`sms` tinyint(1) NOT NULL,
`fax` varchar(100) NOT NULL default '',
`email` varchar(200) NOT NULL default '',
`website` varchar(200) NOT NULL default '',
`last_report_date` datetime default NULL,
`occupants` varchar(100) NOT NULL default '',
`occupants_notes` varchar(255) NOT NULL default '',
`size` varchar(100) NOT NULL default '',
`size_notes` varchar(255) NOT NULL default '',
`price_per_month` varchar(100) NOT NULL default '',
`price_per_month_notes` varchar(255) NOT NULL default '',
`flexible` tinyint(1) NOT NULL default '0',
`move_date` varchar(100) NOT NULL default '',
`move_date_notes` varchar(255) NOT NULL default '',
`duration` varchar(100) NOT NULL default '',
`duration_notes` varchar(255) NOT NULL default '',
`move_reason` varchar(255) NOT NULL default '',
`area_pcode_line` text NOT NULL,
`must_have` varchar(255) NOT NULL default '',
`extra_info` text NOT NULL,
`internet_referral` text NOT NULL,
`active` tinyint(1) NOT NULL default '0',
`extra_it` text NOT NULL,
`other_agents` varchar(255) default NULL,
`enquiry_source_id` text NOT NULL,
`quality_id` mediumint(8) default NULL,
`tel1_report` tinyint(3) default '1',
`tel2_report` tinyint(3) default '1',
`tel3_report` tinyint(3) default '0',
`email_report` tinyint(3) default '1',
`fax_report` tinyint(3) default '0',
`extra_info_report` tinyint(3) default '0',
`found_somewhere_else` tinyint(1) unsigned NOT NULL default '0',
`send_type` tinyint(1) NOT NULL default '0',
`contact_note` varchar(255) default '',
`surrounding_areas` tinyint(1) NOT NULL default '0',
`address_report` tinyint(1) NOT NULL default '0',
`deleted` tinyint(1) NOT NULL default '0',
`deleted_date` datetime default NULL,
`auto_email_date` datetime default '0000-00-00 00:00:00',
`next_auto_email` varchar(100) NOT NULL default '',
`status_id` mediumint(8) NOT NULL default '0',
`nonlive_reason_id` int(3) default NULL,
`auto_email_select` text,
`emails_sent` int(1) default '0',
`registered` datetime default '0000-00-00 00:00:00',
`status_change_date` datetime default '0000-00-00 00:00:00',
`would_like` text,
`building_would_like` text,
`more_one_room` tinyint(1) NOT NULL default '2',
`contact_details_for_report` int(2) NOT NULL default '-1',
`show_prices` tinyint(1) unsigned NOT NULL default '0',
`send_auto` tinyint(1) NOT NULL default '1',
`feedback_session` text,
`login` varchar(32) NOT NULL,
`password` varchar(64) NOT NULL,
`logged_by_date` datetime default NULL,
`notify_sent` tinyint(1) NOT NULL default '0',
`login_enabled` tinyint(1) default '1',
`deactivate_date` datetime default NULL,
`revenue` varchar(50) NOT NULL,
`lead_status` varchar(10) default NULL,
`lead_status_last` varchar(20) NOT NULL,
`fof_id` int(11) default '0',
`is_eps` int(11) default '0',
`is_space` tinyint(4) NOT NULL default '0',
`possible_duplicate` tinyint(1) NOT NULL default '0',
`lead_status_copy` varchar(10) NOT NULL,
`check_duplicate` tinyint(1) default '0',
`spellcheck_off` tinyint(1) default '0',
`seen_before` text NOT NULL,
`been_searching` varchar(250) NOT NULL,
`spoken_to` varchar(250) NOT NULL,
`funding` text NOT NULL,
`need_to_sell` varchar(250) NOT NULL,
`buying_type` tinyint(3) default NULL,
`extra_notes` text NOT NULL,
`terms_offered` text NOT NULL,
`manager_reminder` tinyint(1) default '0',
`manager_reminder_time` datetime default NULL,
`watch` int(1) default NULL,
`watch_reason` varchar(500) default NULL,
`arranging_viewings_sent` tinyint(1) default NULL,
`why_area` varchar(255) NOT NULL,
`website_source` varchar(255) NOT NULL,
`ipaddress` varchar(20) NOT NULL,
`ispossible` tinyint(1) NOT NULL,
`other_source_loose_leads` varchar(255) NOT NULL,
`client_facing` tinyint(1) default NULL,
`nmeeting` varchar(100) NOT NULL,
`document` varchar(15) NOT NULL,
`summary` text NOT NULL,
`golden_hour` tinyint(4) NOT NULL,
`special` tinyint(4) NOT NULL,
`special_second` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `contact_details_for_report` (`contact_details_for_report`),
KEY `original_id` (`original_id`),
KEY `logged_by` (`logged_by`),
KEY `logged_date` (`logged_date`),
KEY `deleted` (`deleted`),
KEY `send_auto` (`send_auto`),
KEY `login` (`login`,`password`),
KEY `logged_by_date` (`logged_by_date`),
KEY `notify_sent` (`notify_sent`),
KEY `login_enabled` (`login_enabled`),
KEY `deactivate_date` (`deactivate_date`),
KEY `lead_status` (`lead_status`(1)),
KEY `fof_id` (`fof_id`),
KEY `is_eps` (`is_eps`),
KEY `possible_duplicate` (`possible_duplicate`),
FULLTEXT KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=1092737 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE applicant_feedback \G;
*************************** 1. row ***************************
Table: applicant_feedback
Create Table: CREATE TABLE `applicant_feedback` (
`id` mediumint(8) unsigned zerofill NOT NULL auto_increment,
`applicant_id` mediumint(8) unsigned zerofill NOT NULL default '00000000',
`manager` tinyint(1) NOT NULL,
`note` text NOT NULL,
`date` date NOT NULL default '0000-00-00',
`time` time NOT NULL default '00:00:00',
`username` varchar(50) default 'system',
`status` varchar(20) NOT NULL,
`comingfrom` varchar(10) NOT NULL,
`send_reminder` tinyint(1) NOT NULL default '0',
`is_show` tinyint(1) NOT NULL,
`price` varchar(20) NOT NULL,
`size` varchar(20) NOT NULL,
`is_possible` tinyint(1) NOT NULL,
`status_id` varchar(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`username`),
KEY `applicant_id` (`applicant_id`),
KEY `is_show` (`is_show`)
) ENGINE=MyISAM AUTO_INCREMENT=567245 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE contact \G;
*************************** 1. row ***************************
Table: contact
Create Table: CREATE TABLE `contact` (
`id` mediumint(8) unsigned zerofill NOT NULL auto_increment,
`parent_id` mediumint(8) unsigned zerofill NOT NULL default '00000000',
`type` varchar(10) NOT NULL default '',
`pronunciation` varchar(100) NOT NULL,
`firstname` varchar(30) NOT NULL default '',
`surname` varchar(30) NOT NULL default '',
`other_names` varchar(30) NOT NULL default '',
`title` varchar(5) NOT NULL default '',
`job_title` varchar(50) default NULL,
`resp_for` varchar(30) NOT NULL default '',
`tel1` varchar(50) NOT NULL default '',
`tel2` varchar(50) NOT NULL default '',
`tel3` varchar(50) NOT NULL default '',
`fax` varchar(50) NOT NULL default '',
`email` varchar(200) NOT NULL default '',
`extra_info` varchar(255) NOT NULL default '',
`sales` tinyint(1) NOT NULL default '0',
`main_contact` tinyint(3) NOT NULL default '0',
`firstname_check` tinyint(1) NOT NULL default '1',
`surname_check` tinyint(1) NOT NULL default '0',
`resp_for_check` varchar(500) NOT NULL,
`department` varchar(50) NOT NULL,
`resp_for_everything` tinyint(1) NOT NULL default '0',
`contractual_arrangements` tinyint(1) NOT NULL default '0',
`marketing` tinyint(1) NOT NULL default '0',
`addedAutomat` tinyint(1) default '0',
`username` varchar(100) NOT NULL,
`password` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `SUR_PAR_ID` (`surname`,`id`),
KEY `department` (`department`),
KEY `resp_for_everything` (`resp_for_everything`,`contractual_arrangements`,`marketing`),
KEY `parent_id` (`parent_id`),
KEY `addedAutomat` (`addedAutomat`)
) ENGINE=MyISAM AUTO_INCREMENT=233806 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE users \G;
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`user_id` smallint(5) unsigned zerofill NOT NULL auto_increment,
`login_id` varchar(30) default NULL,
`password` varchar(16) default NULL,
`email` varchar(60) default NULL,
`is_customer` char(1) NOT NULL default '',
`customer_id` smallint(5) unsigned zerofill NOT NULL default '00000',
`skin` varchar(255) NOT NULL default 'default',
`signature_office` text,
`signature_applicant` text,
`signature_disclaimer` text,
`tel1` varchar(50) NOT NULL,
`mobile` varchar(20) NOT NULL,
`sales_name` varchar(50) default NULL,
`emails_sent_to` int(11) NOT NULL,
`contacts` text NOT NULL,
`sales` int(11) NOT NULL,
PRIMARY KEY (`user_id`),
KEY `login_id` (`login_id`)
) ENGINE=MyISAM AUTO_INCREMENT=167 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
=================================================================================================
On 5.1.41:
mysql> SELECT app.id,leads_notes.note,CONCAT(cnt.title,' ',cnt.firstname,' ',cnt.surname) AS fullname,users.sales_name,app.occupants,app.size,(SELECT CONCAT(appfd.date,' ',appfd.time) AS last_contact_date FROM applicant_feedback appfd WHERE appfd.applicant_id=app.id ORDER BY appfd.id DESC LIMIT 1) AS last_contact_date,DATE_FORMAT(app.manager_reminder_time,' %d/%m/%Y %H:%i:%s') AS manager_reminder_time FROM applicant app RIGHT JOIN users ON users.user_id=app.logged_by RIGHT JOIN contact cnt ON cnt.parent_id=app.id AND cnt.type='APPLICANT' AND cnt.main_contact=1 LEFT JOIN leads_notes ON leads_notes.applicant_id=app.id WHERE app.lead_status_last='hot_big' AND app.status_id <> 2 AND app.status_id <> 0 AND app.manager_reminder='1' AND app.deleted='0' AND manager_reminder_time < '2012-02-09' ORDER by app.manager_reminder_time DESC \G;
330 rows in set (23.75 sec)
mysql> EXPLAIN SELECT app.id,leads_notes.note,CONCAT(cnt.title,' ',cnt.firstname,' ',cnt.surname) AS fullname,users.sales_name,app.occupants,app.size,(SELECT CONCAT(appfd.date,' ',appfd.time) AS last_contact_date FROM applicant_feedback appfd WHERE appfd.applicant_id=app.id ORDER BY appfd.id DESC LIMIT 1) AS last_contact_date,DATE_FORMAT(app.manager_reminder_time,' %d/%m/%Y %H:%i:%s') AS manager_reminder_time FROM applicant app RIGHT JOIN users ON users.user_id=app.logged_by RIGHT JOIN contact cnt ON cnt.parent_id=app.id AND cnt.type='APPLICANT' AND cnt.main_contact=1 LEFT JOIN leads_notes ON leads_notes.applicant_id=app.id WHERE app.lead_status_last='hot_big' AND app.status_id <> 2 AND app.status_id <> 0 AND app.manager_reminder='1' AND app.deleted='0' AND manager_reminder_time < '2012-02-09' ORDER by app.manager_reminder_time DESC \G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: app
type: index_merge
possible_keys: PRIMARY,logged_by,deleted,applicant_manager_reminder,applicant_manager_reminder_time,applicant_lead_status_last,applicant_status_id,applicant_deleted
key: applicant_manager_reminder,applicant_lead_status_last
key_len: 2,22
ref: NULL
rows: 46
Extra: Using intersect(applicant_manager_reminder,applicant_lead_status_last); Using where; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: users
type: eq_ref
possible_keys: PRIMARY,users_user_id
key: PRIMARY
key_len: 2
ref: officeapp.app.logged_by
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: leads_notes
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: officeapp.app.id
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: cnt
type: ref
possible_keys: parent_id,contact_main_contact,contact_type,contact_parent_id
key: parent_id
key_len: 3
ref: officeapp.app.id
rows: 1
Extra: Using where
*************************** 5. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: appfd
type: index
possible_keys: applicant_id,applicant_feedback_applicant_id
key: PRIMARY
key_len: 3
ref: NULL
rows: 1
Extra: Using where
5 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE applicant \G;
*************************** 1. row ***************************
Table: applicant
Create Table: CREATE TABLE `applicant` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`original_id` int(10) unsigned NOT NULL DEFAULT '0',
`external_id` varchar(10) NOT NULL DEFAULT '',
`logged_by` mediumint(8) DEFAULT '2',
`logged_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`company_name` varchar(120) NOT NULL DEFAULT '',
`trading_name` varchar(200) NOT NULL,
`trading_number` varchar(200) NOT NULL,
`business_type` varchar(100) NOT NULL DEFAULT '',
`address1` varchar(100) NOT NULL DEFAULT '',
`address2` varchar(100) NOT NULL DEFAULT '',
`address3` varchar(100) NOT NULL DEFAULT '',
`postcode` varchar(100) NOT NULL DEFAULT '',
`country` varchar(100) NOT NULL DEFAULT '',
`tel1` varchar(100) NOT NULL DEFAULT '',
`tel2` varchar(100) NOT NULL DEFAULT '',
`tel3` varchar(100) NOT NULL DEFAULT '',
`sms` tinyint(1) NOT NULL,
`fax` varchar(100) NOT NULL DEFAULT '',
`email` varchar(200) NOT NULL DEFAULT '',
`website` varchar(200) NOT NULL DEFAULT '',
`last_report_date` datetime DEFAULT NULL,
`occupants` varchar(100) NOT NULL DEFAULT '',
`occupants_notes` varchar(255) NOT NULL DEFAULT '',
`size` varchar(100) NOT NULL DEFAULT '',
`size_notes` varchar(255) NOT NULL DEFAULT '',
`price_per_month` varchar(100) NOT NULL DEFAULT '',
`price_per_month_notes` varchar(255) NOT NULL DEFAULT '',
`flexible` tinyint(1) NOT NULL DEFAULT '0',
`move_date` varchar(100) NOT NULL DEFAULT '',
`move_date_notes` varchar(255) NOT NULL DEFAULT '',
`duration` varchar(100) NOT NULL DEFAULT '',
`duration_notes` varchar(255) NOT NULL DEFAULT '',
`move_reason` varchar(255) NOT NULL DEFAULT '',
`area_pcode_line` text NOT NULL,
`must_have` varchar(255) NOT NULL DEFAULT '',
`extra_info` text NOT NULL,
`internet_referral` text NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '0',
`extra_it` text NOT NULL,
`other_agents` varchar(255) DEFAULT NULL,
`enquiry_source_id` text NOT NULL,
`quality_id` mediumint(8) DEFAULT NULL,
`tel1_report` tinyint(3) DEFAULT '1',
`tel2_report` tinyint(3) DEFAULT '1',
`tel3_report` tinyint(3) DEFAULT '0',
`email_report` tinyint(3) DEFAULT '1',
`fax_report` tinyint(3) DEFAULT '0',
`extra_info_report` tinyint(3) DEFAULT '0',
`found_somewhere_else` tinyint(1) unsigned NOT NULL DEFAULT '0',
`send_type` tinyint(1) NOT NULL DEFAULT '0',
`contact_note` varchar(255) DEFAULT '',
`surrounding_areas` tinyint(1) NOT NULL DEFAULT '0',
`address_report` tinyint(1) NOT NULL DEFAULT '0',
`deleted` tinyint(1) NOT NULL DEFAULT '0',
`deleted_date` datetime DEFAULT NULL,
`auto_email_date` datetime DEFAULT '0000-00-00 00:00:00',
`next_auto_email` varchar(100) NOT NULL DEFAULT '',
`status_id` mediumint(8) NOT NULL DEFAULT '0',
`nonlive_reason_id` int(3) DEFAULT NULL,
`auto_email_select` text,
`emails_sent` int(1) DEFAULT '0',
`registered` datetime DEFAULT '0000-00-00 00:00:00',
`status_change_date` datetime DEFAULT '0000-00-00 00:00:00',
`would_like` text,
`building_would_like` text,
`more_one_room` tinyint(1) NOT NULL DEFAULT '2',
`contact_details_for_report` int(2) NOT NULL DEFAULT '-1',
`show_prices` tinyint(1) unsigned NOT NULL DEFAULT '0',
`send_auto` tinyint(1) NOT NULL DEFAULT '1',
`feedback_session` text,
`login` varchar(32) NOT NULL,
`password` varchar(64) NOT NULL,
`logged_by_date` datetime DEFAULT NULL,
`notify_sent` tinyint(1) NOT NULL DEFAULT '0',
`login_enabled` tinyint(1) DEFAULT '1',
`deactivate_date` datetime DEFAULT NULL,
`revenue` varchar(50) NOT NULL,
`lead_status` varchar(10) DEFAULT NULL,
`lead_status_last` varchar(20) NOT NULL,
`fof_id` int(11) DEFAULT '0',
`is_eps` int(11) DEFAULT '0',
`is_space` tinyint(4) NOT NULL DEFAULT '0',
`possible_duplicate` tinyint(1) NOT NULL DEFAULT '0',
`lead_status_copy` varchar(10) NOT NULL,
`check_duplicate` tinyint(1) DEFAULT '0',
`spellcheck_off` tinyint(1) DEFAULT '0',
`seen_before` text NOT NULL,
`been_searching` varchar(250) NOT NULL,
`spoken_to` varchar(250) NOT NULL,
`funding` text NOT NULL,
`need_to_sell` varchar(250) NOT NULL,
`buying_type` tinyint(3) DEFAULT NULL,
`extra_notes` text NOT NULL,
`terms_offered` text NOT NULL,
`manager_reminder` tinyint(1) DEFAULT '0',
`manager_reminder_time` datetime DEFAULT NULL,
`watch` int(1) DEFAULT NULL,
`watch_reason` varchar(500) DEFAULT NULL,
`arranging_viewings_sent` tinyint(1) DEFAULT NULL,
`why_area` varchar(255) NOT NULL,
`website_source` varchar(255) NOT NULL,
`ipaddress` varchar(20) NOT NULL,
`ispossible` tinyint(1) NOT NULL,
`other_source_loose_leads` varchar(255) NOT NULL,
`client_facing` tinyint(1) DEFAULT NULL,
`nmeeting` varchar(100) NOT NULL,
`document` varchar(15) NOT NULL,
`summary` text NOT NULL,
`golden_hour` tinyint(4) NOT NULL,
`special` tinyint(4) NOT NULL,
`special_second` tinyint(1) NOT NULL,
`previous_applicant_id` mediumint(8) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `contact_details_for_report` (`contact_details_for_report`),
KEY `original_id` (`original_id`),
KEY `logged_by` (`logged_by`),
KEY `logged_date` (`logged_date`),
KEY `deleted` (`deleted`),
KEY `send_auto` (`send_auto`),
KEY `login` (`login`,`password`),
KEY `logged_by_date` (`logged_by_date`),
KEY `notify_sent` (`notify_sent`),
KEY `login_enabled` (`login_enabled`),
KEY `deactivate_date` (`deactivate_date`),
KEY `lead_status` (`lead_status`(1)),
KEY `fof_id` (`fof_id`),
KEY `is_eps` (`is_eps`),
KEY `possible_duplicate` (`possible_duplicate`),
KEY `applicant_manager_reminder` (`manager_reminder`),
KEY `applicant_manager_reminder_time` (`manager_reminder_time`),
KEY `applicant_lead_status` (`lead_status`),
KEY `applicant_lead_status_last` (`lead_status_last`),
KEY `applicant_status_id` (`status_id`),
KEY `applicant_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=1093210 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE applicant_contact \G;
ERROR 1146 (42S02): Table 'officeapp.applicant_contact' doesn't exist
ERROR:
No query specified
mysql> SHOW CREATE TABLE applicant_feedback \G;
*************************** 1. row ***************************
Table: applicant_feedback
Create Table: CREATE TABLE `applicant_feedback` (
`id` mediumint(8) unsigned zerofill NOT NULL AUTO_INCREMENT,
`applicant_id` mediumint(8) unsigned zerofill NOT NULL DEFAULT '00000000',
`manager` tinyint(1) NOT NULL,
`note` varchar(8000) DEFAULT NULL,
`date` date NOT NULL DEFAULT '0000-00-00',
`time` time NOT NULL DEFAULT '00:00:00',
`username` varchar(50) DEFAULT 'system',
`status` varchar(20) NOT NULL,
`comingfrom` varchar(10) NOT NULL,
`send_reminder` tinyint(1) NOT NULL DEFAULT '0',
`is_show` tinyint(1) NOT NULL,
`price` varchar(20) NOT NULL,
`size` varchar(20) NOT NULL,
`is_possible` tinyint(1) NOT NULL,
`status_id` varchar(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`username`),
KEY `applicant_id` (`applicant_id`),
KEY `is_show` (`is_show`),
KEY `applicant_feedback_username` (`username`),
KEY `applicant_feedback_applicant_id` (`applicant_id`),
KEY `applicant_feedback_date` (`date`),
KEY `applicant_feedback_note` (`note`(1000))
) ENGINE=MyISAM AUTO_INCREMENT=572441 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE contact \G;
*************************** 1. row ***************************
Table: contact
Create Table: CREATE TABLE `contact` (
`id` mediumint(8) unsigned zerofill NOT NULL AUTO_INCREMENT,
`parent_id` mediumint(8) unsigned zerofill NOT NULL DEFAULT '00000000',
`type` varchar(10) NOT NULL DEFAULT '',
`pronunciation` varchar(100) NOT NULL,
`firstname` varchar(30) NOT NULL DEFAULT '',
`surname` varchar(30) NOT NULL DEFAULT '',
`other_names` varchar(30) NOT NULL DEFAULT '',
`title` varchar(5) NOT NULL DEFAULT '',
`job_title` varchar(50) DEFAULT NULL,
`resp_for` varchar(30) NOT NULL DEFAULT '',
`tel1` varchar(50) NOT NULL DEFAULT '',
`tel2` varchar(50) NOT NULL DEFAULT '',
`tel3` varchar(50) NOT NULL DEFAULT '',
`fax` varchar(50) NOT NULL DEFAULT '',
`email` varchar(200) NOT NULL DEFAULT '',
`extra_info` varchar(255) NOT NULL DEFAULT '',
`sales` tinyint(1) NOT NULL DEFAULT '0',
`main_contact` tinyint(3) NOT NULL DEFAULT '0',
`firstname_check` tinyint(1) NOT NULL DEFAULT '1',
`surname_check` tinyint(1) NOT NULL DEFAULT '0',
`resp_for_check` varchar(500) NOT NULL,
`department` varchar(50) NOT NULL,
`resp_for_everything` tinyint(1) NOT NULL DEFAULT '0',
`contractual_arrangements` tinyint(1) NOT NULL DEFAULT '0',
`marketing` tinyint(1) NOT NULL DEFAULT '0',
`addedAutomat` tinyint(1) DEFAULT '0',
`username` varchar(100) NOT NULL,
`password` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
KEY `SUR_PAR_ID` (`surname`,`id`),
KEY `department` (`department`),
KEY `resp_for_everything` (`resp_for_everything`,`contractual_arrangements`,`marketing`),
KEY `parent_id` (`parent_id`),
KEY `addedAutomat` (`addedAutomat`),
KEY `contact_main_contact` (`main_contact`),
KEY `contact_type` (`type`),
KEY `contact_parent_id` (`parent_id`)
) ENGINE=MyISAM AUTO_INCREMENT=234625 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE users \G;
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`user_id` smallint(5) unsigned zerofill NOT NULL AUTO_INCREMENT,
`login_id` varchar(30) DEFAULT NULL,
`password` varchar(16) DEFAULT NULL,
`email` varchar(60) DEFAULT NULL,
`is_customer` char(1) NOT NULL DEFAULT '',
`customer_id` smallint(5) unsigned zerofill NOT NULL DEFAULT '00000',
`skin` varchar(255) NOT NULL DEFAULT 'default',
`signature_office` text,
`signature_applicant` text,
`signature_disclaimer` text,
`tel1` varchar(50) NOT NULL,
`mobile` varchar(20) NOT NULL,
`sales_name` varchar(50) DEFAULT NULL,
`emails_sent_to` int(11) NOT NULL,
`contacts` text NOT NULL,
`sales` int(11) NOT NULL,
PRIMARY KEY (`user_id`),
KEY `login_id` (`login_id`),
KEY `users_user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=169 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)