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;
+---------+----------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------------+-----------------------------------+---------------------+---------------------+-----------------------+
| id | note | fullname | sales_name | occupants | size | last_contact_date | manager_reminder_time |
+---------+----------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------------+-----------------------------------+---------------------+---------------------+-----------------------+
| 1090307 | i dont believe they are staying where they are for another year and would defo put in another call at start of feb | Ms Jill Dumas | Robin Thomas | 6-10 | n/a | 2011-12-30 12:48:48 | 08/02/2012 10:25:00 |
...
| 1080416 | need a viewing on this... told mark | Mrs Kat Lee-Ball | Mark Mackay | 18 | 1500sqft | 2012-01-06 09:09:47 | 09/07/2011 09:10:00 |
+---------+----------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------------+-----------------------------------+---------------------+---------------------+-----------------------+
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;
+----+--------------------+-------------+--------+---------------------------+--------------+---------+-------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------------+--------+---------------------------+--------------+---------+-------------------------+-------+----------------------------------------------+
| 1 | PRIMARY | cnt | ALL | parent_id | NULL | NULL | NULL | 54917 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | app | eq_ref | PRIMARY,logged_by,deleted | PRIMARY | 3 | officeapp.cnt.parent_id | 1 | Using where |
| 1 | PRIMARY | users | eq_ref | PRIMARY | PRIMARY | 2 | officeapp.app.logged_by | 1 | Using where |
| 1 | PRIMARY | leads_notes | eq_ref | PRIMARY | PRIMARY | 4 | officeapp.app.id | 1 | |
| 2 | DEPENDENT SUBQUERY | appfd | ref | applicant_id | applicant_id | 3 | officeapp.app.id | 9 | Using where; Using filesort |
+----+--------------------+-------------+--------+---------------------------+--------------+---------+-------------------------+-------+----------------------------------------------+
5 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE applicant;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| applicant | 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;
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| applicant_feedback | 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 leads_notes
-> ;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| leads_notes | CREATE TABLE `leads_notes` (
`applicant_id` int(11) NOT NULL,
`note` varchar(500) NOT NULL,
PRIMARY KEY (`applicant_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE contact;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| contact | 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;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | 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;
+---------+----------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------------+-----------------------------------+-----------------+---------------------+-----------------------+
| id | note | fullname | sales_name | occupants | size | last_contact_date | manager_reminder_time |
+---------+----------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------------+-----------------------------------+-----------------+---------------------+-----------------------+
| 1093124 | NULL | Ms Meng Zhang | Robin Thomas | 4 | 600sqft-800sqft | 2012-02-09 15:07:15 | 08/02/2012 16:35:00 |
...
| Mr Michael Harding | Robin Thomas | 10-20 | n/a | 2011-12-20 17:57:44 | 05/08/2011 18:05:00 |
+---------+----------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------------+-----------------------------------+-----------------+---------------------+-----------------------+
330 rows in set (25.40 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;
+----+--------------------+-------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+-------------------------+------+-----------------------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+-------------------------+------+-----------------------------------------------------------------------------------------------------+
| 1 | PRIMARY | app | index_merge | PRIMARY,logged_by,deleted,applicant_manager_reminder,applicant_manager_reminder_time,applicant_lead_status_last,applicant_status_id,applicant_deleted | applicant_manager_reminder,applicant_lead_status_last | 2,22 | NULL | 46 | Using intersect(applicant_manager_reminder,applicant_lead_status_last); Using where; Using filesort |
| 1 | PRIMARY | users | eq_ref | PRIMARY,users_user_id | PRIMARY | 2 | officeapp.app.logged_by | 1 | Using where |
| 1 | PRIMARY | leads_notes | eq_ref | PRIMARY | PRIMARY | 4 | officeapp.app.id | 1 | |
| 1 | PRIMARY | cnt | ref | parent_id,contact_main_contact,contact_type,contact_parent_id | parent_id | 3 | officeapp.app.id | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | appfd | index | applicant_id,applicant_feedback_applicant_id | PRIMARY | 3 | NULL | 1 | Using where |
+----+--------------------+-------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+-------------------------+------+-----------------------------------------------------------------------------------------------------+
5 rows in set (0.03 sec)
mysql> SHOW CREATE TABLE applicant;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| applicant | 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=1093209 DEFAULT CHARSET=latin1 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE applicant_feedback;
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| applicant_feedback | 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=572437 DEFAULT CHARSET=latin1 |
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE contact;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| contact | 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=234624 DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE users;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | 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)