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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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` (
  `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,
  `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,
  `fax_number` VARCHAR(255) DEFAULT NULL,
  `manager_name` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`lead_id`)


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
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

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 '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

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.

share|improve this answer

Your Answer


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.