3

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');
  • 3
    Can you run a query and share how long it's taking? How did you index on these fields? I don't see the indexes in the table definition. – Valerie Parham-Thompson Dec 9 '14 at 2:03
  • "I tried indexing" - what indexes did you create? Detail in your question is important if you want a better chance of getting useful answers. – David Spillett Aug 26 '15 at 9:31
  • 1
    Why not just create unique indexes on those columns and handle any error resulting from that in the application? – a_horse_with_no_name Jun 11 '16 at 8:33
1

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

0

You need to create indexes on phones fields, and change them to be "not null":

ALTER TABLE `phone_directory` CHANGE `phone1` `phone1` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
CHANGE `phone2` `phone2` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
CHANGE `phone3` `phone3` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
ADD INDEX ( `phone1` ) ,
ADD INDEX ( `phone2` ) ,
ADD INDEX ( `phone3` ) 
  • Its already done. But performance is little bit slower when it has too many connections. I have 4G memory. 2G for InnoDB. Any other ways? Caching help?? – Arun Kumar May 22 '14 at 16:58
  • +1 for adding index but why make the columns NOT NULL? What if someone has only 1 or 2 phones? What should be stored in phone3? (not that the design is very good, with all the duplicate columns, addresses, phones, ...) – ypercubeᵀᴹ Sep 3 '14 at 9:12
0

Well, with these Indexes, there shouldn't be any issue. How many rows does your table have ?

how many joins you use in your queries, If you're using joins, do those tables have appropriate indexes ?

The above mention queries should almost not have any issues. Post the actual queries you're running which are slow.

Although, If you still want to go deeper, I have few points.

  1. if the phone characters will not vary that much, you can keep them CHAR in place of VARCHAR. data fetching with index against CHAR field are certainly faster than that of VARCHAR fields.
  2. This seems to be your master table of employees/clients. If it doesn't require transactions, you can keep it MyISAM, which is faster then InnoDB.
  3. ALTER TABLE tblname ROW_FORMAT=FIXED; This will force VARCHAR to behave as CHAR. (Not recommended). it will increase the size of your table.

NOTE :: Using asterisk in your query is not recommended.

  • "data fetching with index against CHAR field are certainly faster than that of VARCHAR fields" this sounds rather strange. Do you have any proof for that? – a_horse_with_no_name Jun 11 '16 at 7:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.