Some SQL generated by indices in Zena.

gistfile1.sql

SELECT
  nodes.*,
  links.id AS `link_id`,
  links.status AS `l_status`,
  links.comment AS `l_comment`,
  links.date AS `l_date`
FROM
  idx_contracts AS sc1, /* custom index class */
  idx_nodes_ml_strings AS ml1, /* key/value index */
  links, /* relations contract ==> contact */
  nodes, /* returned content (contacts) */
  nodes AS no1 /* contracts */
WHERE
  /* secure access rights */
  nodes.site_id = 1 AND ((nodes.rgroup_id IN (2,4,3,5,1) AND nodes.publish_from <= now() ) OR nodes.wgroup_id IN (2,4,3,5,1))
  /* order by multi-lingual indexed field 'title' */
  AND ml1.lang = 'fr' AND ml1.key = 'title' AND ml1.node_id = nodes.id AND
  /* no tags */
  1 = 1 AND
  /* contract => contact relation */
  nodes.id = links.source_id AND links.relation_id = 4 AND links.target_id = no1.id AND
  /* scope index */
  sc1.contact_contact_search LIKE '%%' AND
  sc1.control_ordered_on < '2010-05-01' AND
  sc1.contact_postal_code = 1306 AND
  /* class filtering: NPPC = Contract */
  no1.id = sc1.node_id AND no1.kpath LIKE 'NPPC%' AND
  /* contracts that are children of current node */
  no1.parent_id = 61613 GROUP BY nodes.id
ORDER BY
  nodes.position ASC, ml1.value ASC
LIMIT 20
OFFSET 0