Mysql – How to optimise read/write performance a MySQL table with many rows

MySQLmysql-5.1performancequery-performance

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

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?

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

Related Solutions

Mysql – the most efficient way to update a product table with 1 million rows in MySQL

What alterable properties of the product table e.g. Indexes might slow down updates?

What do you care? It is totally irrelevant. See, by your own words:

For each row, I need to perform an expensive http operation and then update 1 field in the row based on a returned header.

So, all the MySql performance wont matter - unless you run that on a mobile phone - because you are going to be controleld in speed by "an expenive HTTP operation".

Make a queue (can bedb based), request the http stuff one by one, run updates. THe updates wont take long. Get into multi threaded programming and use some hundred threads - each running in parallel - to get some http speed.

But the update performance in MySQL will not matter unless you want to wait untill you got all data, which may work or not - normally I would say it is not an optimal way.

SQL Server Query Performance – Optimizing Group By with Many Columns

The non-clustered index you have tested is not the best for this query. It can be used for the WHERE clause and for doing an index scan instead of a full table scan but it cannot be used for the GROUP BY.

The best possible index would have to be a partial index (to filter the unwanted rows from the WHERE clause), then have all the columns used in the GROUP BY and then INCLUDE all the other columns used in the SELECT:

CREATE INDEX special_ix 
  ON dbo.Commissions_Output
    ( company, location, account, 
      salesroute, employee, producttype, 
      item, loadjdate, commissionrate ) 
INCLUDE 
  ( [Extended Sales Price], [Delivered Qty] ) 
WHERE 
  ( [Extended Sales Price] <> 0 ) ;

Best Answer

Related Solutions

Mysql – the most efficient way to update a product table with 1 million rows in MySQL

SQL Server Query Performance – Optimizing Group By with Many Columns

Related Question