Re: Best Query Rewrite?

[Rick James]

InfoBright's "column" indexes are great for messy, ad hoc, WHERE clauses. Also it's 10:1 compression helps with I/O. But you have a JOIN.

Your second query taps into InfoBright's strength by giving the WHERE clause something to filter on. The more stuff you can put in a WHERE clause, the more InfoBright will shine brightly.

Give this a try. It may (but probably won't) be better optimized:
T2.timestamp_part = (select timestamp_part from T1) and
T2.imsi = (select imsi from T1)
-->
(T2.timestamp_part, T2.imsi) = (T1.timestamp_part, T1.imsi)


This is generally a bad idea:
`timestamp_date` date DEFAULT NULL,
`timestamp_hour` int(11) DEFAULT NULL,
`timestamp_part` bigint(20) DEFAULT NULL,
It is better to have a single field that is a DATETIME or TIMESTAMP. Every engine can make better use of a single field.

I don't need to comment on unnecessary BIGINT, VARCHAR(255), etc, since InfoBright compression eliminates the unnecessary bulk.

If you were using InnoDB, this would beg for INDEX(imsi, timestamp_part) (in either order):
T2.timestamp_part = T1.timestamp_part and
T2.imsi = T1.imsi

The GROUP BY...ORDER BY COUNT forces all possible rows to be gathered, then sorted (GROUP BY), then sorted again (ORDER BY). Only after that can the LIMIT 50 be brought into play.

Scanning 80M rows takes time, especially with 80M random lookups into the other table.

Perhaps Akiban's magic of "grouping" the two tables together would work better. It should, at least, eliminate the JOIN.

For tables that size, and queries with GROUP BY and COUNT(), I highly recommend building and maintaining "summary table(s)", then querying against them. (I discuss this frequently in the Performance forum.)

Perhaps less than 1% of askers and answerers on this "Newbie" forum have even heard of InfoBright. (I have no hands-on experience.) Check InfoBright's web site.