Re: Select query too slow from merge table of 1 billion records

[Manik Dhawan]

Thanks a lot for the suggestion Rick.

Infact, this was one of the solutions in our mind but I just wanted to check if there`s anything else that is leading to the bottleneck.

Now that you have pointed out the problem, I have one more question.
As you see, we have six indexes on this table. Out of these 6 index fields, c_end_datetime will always be there as part of WHERE clause. So, I can use it as my leftmost key of compound index.
The problem is that remaining 5 indexes can be used in any number i.e. We may use c_end_datetime, c_msisdn_fk,c_apn_name_fk in one query and c_end_datetime,c_apn_name_fk in another.

So, do you think making one wholesome compound key (with c_end_datetime as leftmost column) of six indexes will serve my purpose?

something like

KEY 'wir_msg_idx`(`c_end_datetime`,`c_msisdn_fk`,`c_apn_name_fk`,`c_terminal_type_fk`,`c_terminal_ip`,`c_start_datetime`)

Thanks in advance