Re: Partition Pruning Issue while using Day() function in Range
Posted by:
Rick James
Date: October 09, 2011 01:44PM
Good.
id VARCHAR(2047) -- Really 2047?
If the fields are really that big, suggest normalizing them into another table(s).
KEY `IDX_EVENT_ID` (`id`), -- may not work with such a long field
InnoDB vs MyISAM -- MyISAM would be smaller (2x-3x smaller disk footprint); this may or may not be important.
If your SELECTs are based on these...
> 1.Time and Sender
INDEX(sender, time) -- in that order (see below)
> 2.Time and Recipients
INDEX(recipient, time)
> 3.Time and Sender and Recipients
No extra indexes; the above ones will be somewhat adequate
> 4.Other combinations using Time with filst or mvno
Maybe:
INDEX(mvno, time)
INDEX(filter_list, time)
Those may or may not be useful.
> 5.Time and Subject (mostly part of the subject)
Depends --
If you are using a FULLTEXT index, then you must use MyISAM, etc.
If you are doing exact matches (which seems unlikely), then
INDEX(subject, time)
If you are using LIKE or RLIKE, then no form of INDEX is useful. However, the "partition pruning" (see below) will help.
> 3. How to optimize partial value search on subject or searchable_body column
Describe further what you envision.
See 5, above.
> 4. Compress additional columns data compressed into single column which will result in less storage area. is this a good option ?
It depends.
* Normalizing the 2047-char fields will save some space if they are repeated.
* Cannot compress a field that is used with FULLTEXT.
* A FULLTEXT index is bulky.
> 6. Acceptable response time should be less than 10 seconds
In some cases, queries will be easily less than 1 second. If you need to scan lots of partitions, 10 seconds could be difficult.
How INDEXes and PARTITIONs work:
1. "Partition pruning": If the query includes a restriction on the "partition key" (`time` in your case), then this step picks which PARTITION(s) to look in.
2. Pick one INDEX (eg, (sender, time)) to use. Drill into it.
2a. If the query is WHERE sender = '...' AND time >= ... AND time < ..., then it will use both fields of the index and be very efficient.
2b. Other WHERE clauses may have to scan the entire partition, or some big subset.
Caveat: I do not know if there are restrictions on using FULLTEXT and PARTITION together.
(Sorry, we may need to make another iteration on designing your schema.)