CODEWITHSUNDEEP
mysqlsqldatabase-performance
Kuldeep Singh
Kuldeep Singh

Reputation: 13

How to increase the performance of table having 50 millions records and growing

We have two tables, table A and table B (contains 50 million records). In table A, we stored a unique value of merchant. From that, we fetched records from table B. How to increase the performance of query and what are the ways to increase performance for it?

Note: The table structure for both tables is simple.
Table A - storage engine (MyISAM)
TABLE B - storage engine (MyISAM)
In table A we have one primary key corresponding to table B's many records.
Queries Used:
Query 1: 

records = "select field1, field2...  from table A where merchant_id = ''   
and field_date between '23-06-2012' and '23-06-2015' order by field 1";

Query 2: execute in loop 

  foreach (records as records) {
      "select field_b1, field_b2, .. from table B where field_b1 =    
'records['field1']'"
   }

Upvotes: 0

Views: 1118

Answers (2)

Ovidiu D.
Ovidiu D.

Reputation: 106

If you haven't done it, add indexes on table_a.merchant_id and table_a.field_date. Also add indexes on table_2.field_b1.

In addition, you could try to make the main select on table_b, join it with the records from table_a. Something like:

select 
B.field_b1, B.field_b2 
from table_b AS B 
LEFT JOIN table_a AS A on B.field_b1 = A.field_1
WHERE A.merchant_id = '' AND A.field_date between '23-06-2012' and '23-06-2015' 
order by field 1;

This way you have only one query for all your records instead of 1 main query + who knows how many additional queries for each record found in the first one.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You should rewrite the logic to be a single query. And, use ISO-standard date formats:

select a.field1, a.field2... , b.field1, b.field2, . . 
from a left join
     b
     on a.field1 = b.field1
where a.merchant_id = '' and
      a.field_date between '2012-06-23' and '2015-06-23'
order by a.field1;

For this query, you want indexes on: a(merchant_id, field_date, field1) and b(field1).

Note: if you are only looking for one date, don't use between. Just use =:

select a.field1, a.field2... , b.field1, b.field2, . . 
from a left join
     b
     on a.field1 = b.field1
where a.merchant_id = '' and
      a.field_date = '2012-06-23'
order by a.field1;

The query should run faster.

Upvotes: 0

Related Questions