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
_________________________________________________________________________
------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now. http://p.sf.net/sfu/bobj-july
_______________________________________________
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/
