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 columns


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?

  • 1
    which engine type you are going to use for the table.?Does the table will have high write in future? 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? 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? May 25 '12 at 13:48

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` (
  `domain_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `domain_name` (`domain_name`)
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.

  • If you need any other thing than this please ask. May 25 '12 at 5:31

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.