Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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');
share|improve this question
1  
Could you provide the table structure and the query you are running? –  Jehad Keriaki May 21 at 15:29
    
I have edited the post. Please look into it. –  Arun Kumar May 22 at 6:48
    
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 at 2:03

1 Answer 1

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` ) 
share|improve this answer
    
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 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 at 9:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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