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
    
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 May 22 at 6:48
add comment

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 May 22 at 16:58
add comment

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.