[trendoman]

Oliver,

I am glad that my tip saved you some basic queries

Btw, I tested your cms:func and it failed to create a valid query if used a single field.. So, make sure it works for your fields correctly!

My resulting (not good) query was -
SELECT qty.value as qty, p.id as k_page_id FROM couch_pages p left join couch_data_text qty on qty.page_id = p.id WHERE p.template_id='18' AND p.parent_id=0 AND qty.field_id='303' AND ( ) GROUP BY p.id ORDER BY qty.search_value asc

My params were -

Code: Select all

<cms:capture into='params' is_json='1'>
{
  "masterpage": "index.php",
  "fields": "qty",
  "filters": {
     "qty": {
        ">": "0"
     }
  },
  "order": {
     "qty": "asc"
  }
}
</cms:capture>

Lyrics aside, the next possible solution to your 'summing issue' is a new table! It is a variation of what @KK suggested.

I once did a similar thing for another huge project - created a table with all relevant data and indexed it. For example, I had a reversed 'publish_date' column, so I could use indexes and order pages by date much faster (order by -date ASC <= instead of order by date DESC, which is much slower). I don't remember all the nitty tips and tricks from it (need to find my old code), but the most important part is to create triggers, so the table updates itself automatically (if the fields in regular tables are updated). Any kind of procedures can be created to fetch related pages, fields etc.. and write it into a dedicated cell in that new table. You can see that fetching prepared data from a separate table will be lightning fast.

[trendoman]

@olliwalli, I looked at the snippets. The results you are getting are somewhat
expected - join is always a taxing task, especially with that many of
them.

Without proper environment, I can not do much to test and optimize the query (although I would like to). Please try to play with the following *generic* advice -

• retrieve from database only essentials
• some data can be collected from database into a php array and manipulated there (sum, filter)
• perform operations separately with separate cms:queries where appropriate, then combine via array
• (!) retrieve values from couch_data_text as a main source, avoid heavy couch_pages completely.
• as an "extensive" and cheap measure, invest in a slightly better server, that will produce faster results.

Please let me know what you think.