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

[Rick James]

I would start with 5 indexes (plus PK):
INDEX (`c_msisdn_fk`, c_end_datetime),
INDEX (`c_apn_name_fk`, c_end_datetime),
etc.

That should cover the 1-combo queries, and make a good start on the 2,3,4,5 combos.

I am assuming you only do "=" on the 5 columns and always have a range for c_end_datetime.

How distinctive are the values? Do any increase roughly in sync with end_datetime? We might get some tricks there.

PARTITION is avail in 5.1; it is similar to MERGE, but does "partition pruning". I have not rushed to suggest PARTITION because I don't necessarily see enough added benefit.

If you do change the INDEXes, be sure to do them all in a single ALTER; it will be faster. (Hmmmm... there may be an issue with ALTER and MERGE; I forget.) And be sure to have enough extra disk space.

In a similar situation (DW, various search keys), I built (and maintain) secondary tables with
c_msisdn_fk,
end_datetime -- truncated to the hour,
min_c_msg_id,
max_c_msg_id,
I take 200 rows from the 'fact' table at a time; insert a few rows into this 'lookup' table:

INSERT INTO msisdn_lookup
    SELECT c_msisdn_fk,
           ...(end_datetime)
           MIN(c_msg_id),
           MAX(c_msg_id)
    FROM tb_wir_msg
    WHERE c_msisdn_fk BETWEEN $a AND $a+199
    GROUP BY 1, 2;
  $a += 200;

Plus code to keep track of where I "left off", etc.

Then the desired query takes two steps. The first finds some general areas to probe, the second does the actual probes...

1. Select min_c_msg_id, max_c_msg_id pairs for the time range and msisdn value(s) desired.
2. Build a UNION of SELECTs with c_msisdn_fk BETWEEN ... (Note: UNION works much better than OR.) (PHP code used to build the UNION.) Sometimes I get 2x improvement, sometimes 10000x improvement in how much of the fact table I have to scan.

Take a look at InfoBright; it's great for ad hoc queries like yours.