User Tools

Site Tools


adv_sample_user_queries_plus

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
Last revisionBoth sides next revision
adv_sample_user_queries_plus [2014/08/30 23:23] danieladv_sample_user_queries_plus [2021/05/15 12:18] – index optimisation daniel
Line 1: Line 1:
-==== Advanced sample 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 22: Line 22:
 </code> </code>
  
 +=== Old messages, not archived or trashed, that belong to a thread whose most recent message  is older than one month: ===
 +<code sql>
 +SELECT mail_id FROM mail m
 +WHERE status&32=0 AND status&16 -- intentionally split to benefit from the index on mail.status&32=0
 +AND msg_date<now()-'1 month'::interval
 +AND thread_id is not null
 +AND not exists
 + (SELECT 1 FROM mail m2
 +  WHERE m2.thread_id=m.thread_id
 +    AND m2.msg_date>now()-'1 month'::interval)
 +</code>
 ---- ----
  
 See the [[http://www.manitou-mail.org/doc/mdx/database-schema.html|database schema documentation]] for definitions of tables and columns. See the [[http://www.manitou-mail.org/doc/mdx/database-schema.html|database schema documentation]] for definitions of tables and columns.
  
adv_sample_user_queries_plus.txt · Last modified: 2021/05/15 12:21 by daniel