I just looked at this on 2.6.4, and its still the same.
if you use partition_tags, this won't use a key.
Michael Scheidell wrote:
> Im still running 2.6.3.. :-)
>
> found this in $sql_clause
> 'sel_quar' =>
> 'SELECT mail_text FROM quarantine'.
> ' WHERE coalesce(partition_tag,0)=coalesce(?,0) AND mail_id=?'.
> ' ORDER BY chunk_ind',
>
> if you use partitions, (real partitions) partition_tag has to be part
> of the key, so, quarantine index needed to change from:
> mail_id,chunk_ind
>
> to either
> partition_tag,mail_id,chunk_ind
> or
> mail_id,chunk_ind, partition_tag
>
> the above query translates to this (note the '' around partition tag,
> so if partition tag is 38, you get this:
>
>
> SELECT mail_text FROM quarantine
> WHERE coalesce(partition_tag,0)=coalesce('38',0) AND
> mail_id='x+kjsl..Aljl'
> ORDER BY chunk_ind;
>
> explain SELECT mail_text FROM quarantine WHERE
> coalesce(partition_tag,0)=coalesce('200937',0) AND
> mail_id='KLx2krWNgDIc' ORDER BY chunk_ind;
> +----+-------------+------------+------+---------------+------+---------+------+---------+-----------------------------+
>
>
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> +----+-------------+------------+------+---------------+------+---------+------+---------+-----------------------------+
>
>
> | 1 | SIMPLE | quarantine | ALL | NULL | NULL |
> NULL | NULL | 1420404 | Using where; Using filesort |
> +----+-------------+------------+------+---------------+------+---------+------+---------+-----------------------------+
>
>
> 1 row in set (0.00 sec)
>
>
> (note that if you coalesce on RHS of equasion, mysql won't use an index.
> second, a in ('38',0) uses a string and a binary, and won't use an
> index.
>
> needed to change the query thusly in amavisd.conf: (given that the
> schema suggested used a default partition_tag of 0, there would never
> have been a NULL)
> also, Im not sure you need order by chunk_ind as chunk_ind is in the key
>
> $sql_clause{sel_quar}=
> "SELECT mail_text FROM quarantine".
> " WHERE partition_tag in('0',?) AND mail_id=?".
> " AND chunk_ind > 0".
> " ORDER BY chunk_ind";
>
>
> explain SELECT mail_text FROM quarantine WHERE partition_tag
> in('0','200938') AND mail_id='WqOfJlW+AG-k' ORDER BY chunk_ind;
> +----+-------------+------------+-------+---------------+---------+---------+------+------+-----------------------------+
>
>
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> +----+-------------+------------+-------+---------------+---------+---------+------+------+-----------------------------+
>
>
> | 1 | SIMPLE | quarantine | range | PRIMARY | PRIMARY |
> 22 | NULL | 14 | Using where; Using filesort |
> +----+-------------+------------+-------+---------------+---------+---------+------+------+-----------------------------+
>
>
> 1 row in set (0.01 sec)
>
> mysql> explain SELECT mail_text FROM quarantine WHERE partition_tag
> in('0','200938') AND mail_id='WqOfJlW+AG-k';
> +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
>
>
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
>
>
> | 1 | SIMPLE | quarantine | range | PRIMARY | PRIMARY |
> 22 | NULL | 14 | Using where |
> +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
>
>
> 1 row in set (0.00 sec)
>
>
> and, in fact, if there is a mail_id match in partition tag 0, AND
> 200938, this will in fact mix them, (actually, I don't know what will
> happen if you get two anyway)
>
> is is true that with this index, you will ALWAYS get it by chunk_ind
> order?
>
> PRIMARY KEY (`partition_tag`,`mail_id`,`chunk_ind`)
>
> select chunk_ind from quarantine WHERE partition_tag in('0','200938')
> AND mail_id='WqOfJlW+AG-k';
> +-----------+
> | chunk_ind |
> +-----------+
> | 1 |
> | 2 |
> | 3 |
> | 4 |
> | 5 |
> | 6 |
> | 7 |
> | 8 |
> | 9 |
> | 10 |
> | 11 |
> | 12 |
> | 13 |
> +-----------+
> 13 rows in set (0.00 sec)
>
--
Michael Scheidell, CTO
Phone: 561-999-5000, x 1259
> *| *SECNAP Network Security Corporation
* Certified SNORT Integrator
* 2008-9 Hot Company Award Winner, World Executive Alliance
* Five-Star Partner Program 2009, VARBusiness
* Best Anti-Spam Product 2008, Network Products Guide
* King of Spam Filters, SC Magazine 2008
_________________________________________________________________________
This email has been scanned and certified safe by SpammerTrap(r).
For Information please see http://www.spammertrap.com
_________________________________________________________________________
------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
AMaViS-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
