Huge performance issue with landing page data views

Product Support
1

We have been seeing big problems when trying to access (via the Mautic admin pages) the stats for landing pages. We have a page that has around 10,000 hits, and are unable to view the stats - the admin panel just hangs. We looked at the SQL query that was being used by the page and the problem seems to be with the subqueries, we were able to achieve the same output using the below query in a fraction of the time:



CREATE TEMPORARY TABLE IF NOT EXISTS tmp_0 AS (

SELECT

b.tracking_id

FROM

page_hits b

LEFT JOIN

pages p

ON

b.page_id = p.id

WHERE

b.code = 200

GROUP BY

b.tracking_id

HAVING

COUNT(DISTINCT(b.page_id)) = 1

);

SELECT

h.page_id,

count(distinct(h.tracking_id)) AS bounces

FROM

page_hits h

INNER JOIN

tmp_0

ON

h.tracking_id = tmp_0.tracking_id

AND

h. page_id IN (19)

GROUP BY

h.page_id

;

DROP TEMPORARY TABLE tmp_0;







Can you please review this and implement a revised SQL query - currently the landing pages stats element of Mautic is now currently unusable for us.

2

The tricky part with this is because we use Doctrine ORM to support multiple database platforms, I’m not sure Doctrine’s ORM classes will allow us to easily construct such a query.

3

Alan - if this sort of query is tricky with Doctrine, could you investigate alternate solutions to the query issue - currently this part of Mautic is now unusable for us.

4

We have been seeing big problems when trying to access (via the Mautic admin pages) the stats for landing pages. We have a page that has around 10,000 hits, and are unable to view the stats - the admin panel just hangs. We looked at the SQL query that was being used by the page and the problem seems to be with the subqueries, we were able to achieve the same output using the below query in a fraction of the time:

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_0 AS (
SELECT
b.tracking_id
FROM
page_hits b
LEFT JOIN
pages p
ON
b.page_id = p.id
WHERE
b.code = 200
GROUP BY
b.tracking_id
HAVING
COUNT(DISTINCT(b.page_id)) = 1
);
SELECT
h.page_id,
count(distinct(h.tracking_id)) AS bounces
FROM
page_hits h
INNER JOIN
tmp_0
ON
h.tracking_id = tmp_0.tracking_id
AND
h. page_id IN (19)
GROUP BY
h.page_id
;
DROP TEMPORARY TABLE tmp_0;

Can you please review this and implement a revised SQL query - currently the landing pages stats element of Mautic is now currently unusable for us.

5

That’s interesting. Would you mind to send a PR with your changes to official GH repo?

https://github.com/mautic/mautic

6

Unfortunately it seems Doctrine itself doesn’t support temporary tables at the moment (see http://www.doctrine-project.org/jira/browse/DBAL-1145), so a native solution using Doctrine’s API is out of the question.

One possible alternative is to just feed Doctrine raw queries. In the code where these queries are being generated, the subquery would need to be wrapped in the create table statement then the outer query adjusted a bit to match the changes needed to query the temporary table. Theoretically this change is possible to accomplish but will need some heavy testing and review to see through to completion.

7

I make no promises this is immediately working, but https://github.com/mautic/mautic/pull/1103 makes an attempt to restructure those queries.