mysql performance issue

Question

I have table named as contacts which has nearly 1.2 million records we use MyIsam engine whenever we query this table mysql hangs down so now we are trying our hands with Innodb engine so that if it slows down, but it will not hang up for others

So we want make fast with Myisam we tried many indexes on this table but it goes down and hangs the system

What should be done to make it more faster and it should not hang up the system

This is the table:

CREATE TABLE `contacts` (
  `id` varchar(36) NOT NULL,
  `deleted` tinyint(1) NOT NULL default '0',
  `date_entered` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_modified` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified_user_id` varchar(36) default NULL,
  `assigned_user_id` varchar(36) default NULL,
  `created_by` varchar(36) default NULL,
  `team_id` varchar(36) default NULL,
  `salutation` varchar(5) default NULL,
  `first_name` varchar(100) default '',
  `last_name` varchar(100) default '',
  `username` varchar(25) default '',
  `lead_source` varchar(100) default NULL,
  `title` varchar(50) default NULL,
  `department` varchar(100) default NULL,
  `reports_to_id` varchar(36) default NULL,
  `birthdate` date default NULL,
  `do_not_call` char(3) default '0',
  `phone_home` varchar(25) default NULL,
  `phone_mobile` varchar(25) default NULL,
  `phone_work` varchar(25) default '',
  `phone_other` varchar(25) default NULL,
  `phone_fax` varchar(25) default '',
  `email1` varchar(100) default '',
  `email2` varchar(100) default NULL,
  `assistant` varchar(75) default NULL,
  `assistant_phone` varchar(25) default NULL,
  `email_opt_out` char(3) default 'off',
  `primary_address_street` varchar(150) default NULL,
  `primary_address_city` varchar(100) default NULL,
  `primary_address_state` varchar(100) default NULL,
  `primary_address_postalcode` varchar(20) default NULL,
  `primary_address_country` varchar(100) default NULL,
  `alt_address_street` varchar(150) default NULL,
  `alt_address_city` varchar(100) default NULL,
  `alt_address_state` varchar(100) default NULL,
  `alt_address_postalcode` varchar(20) default NULL,
  `alt_address_country` varchar(100) default NULL,
  `description` text,
  `portal_name` varchar(255) default NULL,
  `portal_active` tinyint(1) NOT NULL default '0',
  `portal_app` varchar(255) default NULL,
  `salesforceid` varchar(36) default NULL,
  `phone_direct` varchar(25) default NULL,
  `invalid_email` tinyint(1) default '0',
  `parent_is_lead` char(3) default 'no',
  `advisory_board_member` varchar(25) default NULL,
  `direct_marketing` varchar(25) default NULL,
  `efx_id` varchar(36) default NULL,
  `fax_opt_out` char(3) default 'off',
  `ppc_keyword` varchar(50) default NULL,
  `status` varchar(25) default NULL,
  `web_form` varchar(50) default NULL,
  `efx_export_date` datetime default NULL,
  `bmtn` varchar(225) default '',
  `employee_location` varchar(50) default NULL,
  `pronunciation` varchar(250) default NULL,
  `duplicate_of` varchar(36) default NULL,
  `job_category` varchar(50) default NULL,
  `last_ska_upload_key` varchar(50) default NULL,
  `persid` varchar(36) default NULL,
  `last_web_upload_key` varchar(50) default NULL,
  `last_webinar_upload_key` varchar(50) default NULL,
  `primary_address_latitude` float default NULL,
  `primary_address_longitude` float default NULL,
  `first_name_soundex` varchar(30) default NULL,
  `last_name_soundex` varchar(30) default NULL,
  `primary_address_street_soundex` varchar(30) default NULL,
  `campaign_id` varchar(36) default NULL,
  `portal_password` varchar(32) default NULL,
  `pss_branch` varchar(40) default NULL,
  `pss_id` int(12) default NULL,
  `source_detail` varchar(100) default NULL,
  `source` varchar(100) default NULL,
  `pss_region` varchar(30) default NULL,
  `source_added` datetime default NULL,
  `terminated_user` char(3) default 'off',
  `invite_opt_out` char(3) default 'off',
  `newsletter_opt_out` char(3) default 'off',
  `stream_opt_out` char(3) default 'off',
  PRIMARY KEY  (`id`),
  KEY `idx_contacts_del_last` (`deleted`,`last_name`),
  KEY `idx_cont_del_reports` (`deleted`,`reports_to_id`,`last_name`),
  KEY `idx_contact_del_team` (`deleted`,`team_id`),
  KEY `idx_contact_salesforceid` (`salesforceid`),
  KEY `idx_contacts_username` (`username`),
  KEY `idx_email_opt_out` (`email_opt_out`),
  KEY `idx_primary_address_street` (`primary_address_street`),
  KEY `idx_primary_address_city` (`primary_address_city`),
  KEY `idx_primary_address_state` (`primary_address_state`),
  KEY `idx_primary_address_postalcode` (`primary_address_postalcode`),
  KEY `idx_primary_address_country` (`primary_address_country`),
  KEY `idx_modified_user_id` (`modified_user_id`),
  KEY `idx_assigned_user_id` (`assigned_user_id`),
  KEY `idx_created_by` (`created_by`),
  KEY `idx_team_id` (`team_id`),
  KEY `idx_reports_to_id` (`reports_to_id`),
  KEY `idx_contacts_efx_id` (`efx_id`),
  KEY `idx_contacts_title1` (`title`,`deleted`),
  KEY `idx_contacts_email1` (`email1`),
  KEY `idx_contacts_email2` (`email2`),
  KEY `idx_contacts_job_category` (`job_category`),
  KEY `idx_contacts_first_name_sdx` (`first_name_soundex`),
  KEY `idx_contacts_primary_street_sdx` (`primary_address_street_soundex`),
  KEY `idx_contacts_last_name_sdx` (`last_name_soundex`),
  KEY `idx_contacts_portal_name` (`portal_name`),
  KEY `idx_contacts_portal_active` (`portal_active`),
  KEY `idx_contacts_del_last_first` (`deleted`,`last_name`,`first_name`),
  KEY `idx_contacts_del_first` (`deleted`,`first_name`),
  KEY `idx_pss_id` (`pss_id`),
  KEY `idx_phone_work_last_name_first_name_deleted` (`phone_work`,`last_name`,`first_name`,`deleted`),
  KEY `idx_phone_work_last_name_first_name_deleted_sdx` (`phone_work`,`last_name_soundex`,`first_name_soundex`,`deleted`),
  KEY `idx_email1_last_name_first_name_deleted` (`email1`,`last_name`,`first_name`,`deleted`),
  KEY `idx_email1_last_name_first_name_deleted_sdx` (`email1`,`last_name_soundex`,`first_name_soundex`,`deleted`),
  KEY `idx_phone_fax_last_name_first_name_deleted` (`phone_fax`,`last_name`,`first_name`,`deleted`),
  KEY `idx_phone_fax_last_name_first_name_deleted_sdx` (`phone_fax`,`last_name_soundex`,`first_name_soundex`,`deleted`),
  KEY `idx_phone_work_last_name_deleted` (`phone_work`,`last_name`,`deleted`),
  KEY `idx_phone_work_last_name_deleted_sdx` (`phone_work`,`last_name_soundex`,`deleted`),
  KEY `idx_email1_last_name_deleted` (`email1`,`last_name`,`deleted`),
  KEY `idx_email1_last_name_deleted_sdx` (`email1`,`last_name_soundex`,`deleted`),
  KEY `idx_phone_fax_last_name_deleted` (`phone_fax`,`last_name`,`deleted`),
  KEY `idx_phone_fax_last_name_deleted_sdx` (`phone_fax`,`last_name_soundex`,`deleted`),
  KEY `idx_email1_first_name_deleted` (`email1`,`first_name`,`deleted`),
  KEY `idx_email1_first_name_deleted_sdx` (`email1`,`first_name_soundex`,`deleted`),
  KEY `idx_phone_fax_first_name_deleted` (`phone_fax`,`first_name`,`deleted`),
  KEY `idx_phone_fax_first_name_deleted_sdx` (`phone_fax`,`first_name_soundex`,`deleted`),
  KEY `idx_email1_deleted` (`email1`,`deleted`),
  KEY `idx_last_name_first_name_deleted_sdx` (`last_name_soundex`,`first_name_soundex`,`deleted`),
  KEY `idx_phone_mobile_deleted` (`phone_mobile`,`deleted`,`id`),
  KEY `idx_first_name_bmtn` (`first_name`,`bmtn`),
  KEY `idx_first_name_bmtn_email1` (`first_name`,`bmtn`,`email1`),
  KEY `idx_bmtn_email1` (`bmtn`,`email1`),
  KEY `idx_deleted` (`deleted`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-

SELECT acc.id, acc.name, con_reports_to.first_name, con_reports_to.last_name
from contacts
left join accounts_contacts a_c on a_c.contact_id = '9802f40d-78bb-8dd4-dfaa-43f1064ccd5e' and a_c.deleted=0
left join accounts acc on a_c.account_id = acc.id and acc.deleted=0
left join contacts con_reports_to on con_reports_to.id = contacts.reports_to_id
where contacts.id = '9802f40d-78bb-8dd4-dfaa-43f1064ccd5e'

Answer 1

I suspect the assertion "whenever we query this table mysql hangs down" is an overbid -- for example, with MyISAM, SELECT COUNT(*) FROM TheTable should be very fast, essentially "no matter what". Sure, some queries will be slow -- especially if the table is not indexed properly for the queries, or if MySQL's alleged optimizer is picking the wrong strategy (but you could give it hints).

Why don't you show us the CREATE TABLE (including indices), a couple of the queries that take too long, ideally a precise measure of how long they take, and the output of EXPLAIN SELECT (&c) for those couple queries -- I bet we could really be of some help then!

Edit: the CREATE TABLE essentially shows that the table is just too "broad" -- far too many columns -- to expect decent performance (even though no queries were shown). The schema needs a redesign, breaking up chunks of this huge monolithic table (e.g., the address-related information) into other auxiliary tables. Exactly how to best do it depends entirely on the queries that are most important to optimize, so, not knowing the queries in question, I'm not even going to attempt the task.

Edit again: so the query has been posted and uses other tables, accounts and account_contacts, as well as the hugely broad contacts one described; the query as posted (trying to make sense of it by formatting &c) is:

SELECT acc.id, acc.name, con_reports_to.first_name, con_reports_to.last_name
FROM contacts 
LEFT JOIN accounts_contacts a_c 
          ON a_c.contact_id = '9802f40d-78bb-8dd4-dfaa-43f1064ccd5e' AND
             a_c.deleted=0 
LEFT JOIN accounts acc 
          ON a_c.account_id = acc.id AND 
             acc.deleted=0
LEFT JOIN contacts con_reports_to 
          ON con_reports_to.id = contacts.reports_to_id
WHERE contacts.id = '9802f40d-78bb-8dd4-dfaa-43f1064ccd5e'

Why the LEFT JOINs here instead of normal INNER joins? Is it possible in each case that there's no corresponding row on the right-hand-side table? For example, if there's no line in a_c with the given values for contact_id and deleted, then all the fields of a_c in the first LEFT JOIN will be NULL, so there can be no correspondence for acc either: is it important to emit NULL, NULL as the first two columns in this case? Moreover the JOIN conditions for a_c an acc make no reference at all to contacts, so this will be a cartesian product: every line selected from acc, if any, will pair up with every line selected from con_reports_to. So the a_c/acc query could be entirely separated from the one on contacts and con_reports, presumably ligthtening the query considerably (the two logically separate results could of course easily be put together again in the client).

What does EXPLAIN SELECT say for this complex query and what does it say for the two lighter-weight separate ones I'm suggesting? What indices are on the accounts and account_contact tables?

Answer 2

horizontal splitting? though i guess 1.2 million records are not that much to introduce horizontal splitting.. try to locate the bottom neck... also the problem may lie with your hardware as well for example harddisk almost full etc.