Mysql – Improve MySQL Query Performance – Too many records

indexMySQLperformancequery-performancerdbms

I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during administration process. My server load drastically increases when I do search for phone numbers in the entire database. Since I dont want to keep duplicate phone records in the database, I used to check the existence of phone number in the whole database, which cause my application a little bit slower. My Question: How do I improve querying the entire database with high performance.

phone1 -> Datatype Varchar(10)

I tried indexing and it do a little for me. Any other ways to improve performance of my system.

Table Structure:

CREATE TABLE `phone_directory` (
  `lead_id` INT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
  `list_id` BIGINT(14) UNSIGNED DEFAULT NULL,
  `gmt_offset_now` DECIMAL(4,2) DEFAULT '0.00',
  `first_name` VARCHAR(30) DEFAULT NULL,
  `middle_initial` CHAR(1) DEFAULT NULL,
  `last_name` VARCHAR(30) DEFAULT NULL,
  `address1` VARCHAR(100) DEFAULT NULL,
  `address2` VARCHAR(100) DEFAULT NULL,
  `address3` VARCHAR(100) DEFAULT NULL,
  `city` VARCHAR(50) DEFAULT NULL,
  `state` CHAR(2) DEFAULT NULL,
  `postal_code` VARCHAR(10) DEFAULT NULL,
  `phone1` VARCHAR(12) DEFAULT NULL,
  `phone2` VARCHAR(12) DEFAULT NULL,
  `phone3` VARCHAR(12) DEFAULT NULL,
  `email` VARCHAR(70) DEFAULT NULL,
  `fax_number` VARCHAR(255) DEFAULT NULL,
  `manager_name` VARCHAR(255) DEFAULT NULL,
  `status` VARCHAR(6) DEFAULT NULL,
  PRIMARY KEY (`lead_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Query: 

SELECT * FROM phone_directory WHERE phone1 IN ('315XXXXXXX','0315XXXXXXX');

SELECT * FROM phone_directory WHERE phone2 IN ('315XXXXXXX','0315XXXXXXX');

SELECT * FROM phone_directory WHERE phone3 IN ('315XXXXXXX','0315XXXXXXX');

Best Answer

I'd recommend to not have three columns phone1, phone2 and phone3, but have a related table for phones, and have your data fully normalized (or, further normalized):

 CREATE TABLE `phone_directory` (
   `lead_id` INT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
   `list_id` BIGINT(14) UNSIGNED DEFAULT NULL,
   `gmt_offset_now` DECIMAL(4,2) DEFAULT '0.00',
   `first_name` VARCHAR(30) DEFAULT NULL,
   `middle_initial` CHAR(1) DEFAULT NULL,
   `last_name` VARCHAR(30) DEFAULT NULL,
   `address1` VARCHAR(100) DEFAULT NULL,
   `address2` VARCHAR(100) DEFAULT NULL,
   `address3` VARCHAR(100) DEFAULT NULL,
   `city` VARCHAR(50) DEFAULT NULL,
   `state` CHAR(2) DEFAULT NULL,
   `postal_code` VARCHAR(10) DEFAULT NULL,
   `email` VARCHAR(70) DEFAULT NULL,
   `fax_number` VARCHAR(255) DEFAULT NULL,
   `manager_name` VARCHAR(255) DEFAULT NULL,
   `status` VARCHAR(6) DEFAULT NULL,
   PRIMARY KEY (`lead_id`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8;

 CREATE TABLE phones
 (
     lead_id INT(9) NOT NULL REFERENCES phone_directory(lead_id),
     phone VARCHAR(12) NOT NULL,  
     priority tinyint DEFAULT 1,  -- If you need to give them priorities (1, 2, 3, ...), or sort them
     CONSTRAINT unique_phones UNIQUE(phone), -- You don't want repetead telephones. This enforces it.
     PRIMARY KEY(lead_id, phone)  -- Covering index + clustering... for the sake of efficiency
 ) ;

Your check now is only:

SELECT 
    lead_id, phone 
FROM 
    phones 
WHERE 
    phone IN ('315XXXXXXX','0315XXXXXXX');

On average, this will be 3x faster ... and you can have 0, 1, 2, 3 or any number of phones for a given lead_id, and your UNIQUE constraint enforces uniqueness, so, if there's an error at some point in your application, the database helps you avoid a mistake.

dbfiddle here

NOTE 1: It is not strange to have one company have more than 3 telephone numbers: you're covering all bases.

NOTE 2: As phone numbers aren't going to use non-ASCII characters, you could save some space (and increase slightly in speed, as less data is going to move around) by specifying a single-char collation, such as latin1_bin.

dbfiddle here

Related Question