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 developing a django application where I use a MySQL database. In my application there is one table for domains information which has only 2 coloumns

id
domain_name

but I have a problem when I have imported almost 14,150,000 rows in that table. I have not done any optimisations yet (using default settings) and there is also a possibility of importing another 14,123,500 records.

Right now I am not able read that data in my application.

Which steps should I follow to perform a read,search operation faster?

share|improve this question
1  
which engine type you are going to use for the table.?Does the table will have high write in future? –  Abdul Manaf May 25 '12 at 5:17
1  
What do you mean with search? Query: WHERE DomainName = 'www.stackoverflow.com' or WHERE DomainName LIKE '%.stackoverflow.com' ? Or something else? –  ypercube May 25 '12 at 10:37
    
We do need more information to be able to help. What query are you trying to perform (and can you put the EXPLAIN SELECT... output in the question?) What Storage Engine are you using? –  Derek Downey May 25 '12 at 13:48
add comment

1 Answer

As you need to perform a read,search operation faster you can use the MyISAM as the table engine if the table will not have the High write's in future.

When you will use the MyISAM Engine for fast read/search for this table ,you need to set the Key_Buffer_size to some appropriate value depending upon the index size of your MyISAM tables and also the amount of available RAM.

As the Table having two columns id and domain name, so you can use the FULL TEXT index on domain name so it will make searching fast on the column domain name.

Look the structure which I have Created

mysql> show create table domain\G
*************************** 1. row ***************************
       Table: domain
Create Table: CREATE TABLE `domain` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `domain_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `domain_name` (`domain_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Restore the data in table and find out index_length for MyISAM table and set the Key_Buffer_size accordingly.

See How to set Key Buffer Size.

share|improve this answer
    
If you need any other thing than this please ask. –  Abdul Manaf May 25 '12 at 5:31
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.