MySQL Forums
Forum List  »  MyISAM

Re: Select query too slow from merge table of 1 billion records
Posted by: Rick James
Date: February 24, 2010 08:59PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Select query too slow from merge table of 1 billion records
3680
February 24, 2010 08:59PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.