adv_sample_user_queries_plus
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
adv_sample_user_queries_plus [2016/03/13 22:24] – daniel | adv_sample_user_queries_plus [2021/05/15 12:21] (current) – daniel | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ==== Advanced examples of SQL queries ==== | + | ==== Advanced examples of SQL queries ==== |
=== Messages sent, with senders or recipients from a specific domain, but not tagged with a specified tag: === | === Messages sent, with senders or recipients from a specific domain, but not tagged with a specified tag: === | ||
Line 25: | Line 25: | ||
<code sql> | <code sql> | ||
SELECT mail_id FROM mail m | SELECT mail_id FROM mail m | ||
- | WHERE status&(16+32)=0 | + | WHERE status&32=0 AND status&16 -- intentionally split to benefit from the index on mail.status& |
AND msg_date< | AND msg_date< | ||
AND thread_id is not null | AND thread_id is not null | ||
Line 34: | Line 34: | ||
</ | </ | ||
---- | ---- | ||
+ | |||
+ | === Messages from the thread with the most unprocessed messages === | ||
+ | <code sql> | ||
+ | select mail_id from mail where thread_id in | ||
+ | (select thread_id | ||
+ | from mail | ||
+ | where status& | ||
+ | and thread_id is not null | ||
+ | group by thread_id | ||
+ | order by count(*) desc | ||
+ | limit 1 | ||
+ | ) | ||
+ | </ | ||
See the [[http:// | See the [[http:// | ||
adv_sample_user_queries_plus.1457907869.txt.gz · Last modified: 2016/03/13 22:24 by daniel