Hello,
I'm trying to run this query more fastly. Can you help me how to optimalize it?
MariaDB [commonswiki_p]> select count(*) from logging_logindex where log_type="thanks" and log_title="Martin_Urbanec"; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (8 min 42.87 sec)
MariaDB [commonswiki_p]>
It's supposed to provide web-accessible information, and it's not possible to wait 8 mins for completion.
https://tools.wmflabs.org/sql-optimizer/ didn't offer any solution.
Any suggestions?
Martin
Hello,
On Mon, Jun 3, 2019 at 7:31 PM Martin Urbanec martin.urbanec@wikimedia.cz wrote:
Hello,
I'm trying to run this query more fastly. Can you help me how to optimalize it?
MariaDB [commonswiki_p]> select count(*) from logging_logindex where log_type="thanks" and log_title="Martin_Urbanec"; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (8 min 42.87 sec)
could you paste the EXPLAIN output? You can generate that with Quarry (or thought the command line with `SHOW EXPLAIN FOR <connection>`). That may help us providing alternative query methods or seeing if an index is missing.
Without looking at the query or the table, I wonder if it is missing a namespace and that is why it may be doing a full scan (explain may help)?
To use those indexes on title you also need to filter by log_namespace: MariaDB [commonswiki_p]> select count(*) from logging_logindex where log_type="thanks" and log_title="Martin_Urbanec" and log_namespace = 2; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.03 sec)
On Mon, 3 Jun 2019 at 18:47, Jaime Crespo jcrespo@wikimedia.org wrote:
Hello,
On Mon, Jun 3, 2019 at 7:31 PM Martin Urbanec martin.urbanec@wikimedia.cz wrote:
Hello,
I'm trying to run this query more fastly. Can you help me how to
optimalize it?
MariaDB [commonswiki_p]> select count(*) from logging_logindex where
log_type="thanks" and log_title="Martin_Urbanec";
+----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (8 min 42.87 sec)
could you paste the EXPLAIN output? You can generate that with Quarry (or thought the command line with `SHOW EXPLAIN FOR <connection>`). That may help us providing alternative query methods or seeing if an index is missing.
Without looking at the query or the table, I wonder if it is missing a namespace and that is why it may be doing a full scan (explain may help)?
Wikimedia Cloud Services mailing list Cloud@lists.wikimedia.org (formerly labs-l@lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud
Hello Martin, Adding "and log_namespace = 2" to the where clause solves the performance problem. Regards.
On Monday, June 3, 2019, 8:31:44 PM GMT+3, Martin Urbanec martin.urbanec@wikimedia.cz wrote:
Hello, I'm trying to run this query more fastly. Can you help me how to optimalize it? MariaDB [commonswiki_p]> select count(*) from logging_logindex where log_type="thanks" and log_title="Martin_Urbanec"; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (8 min 42.87 sec)
MariaDB [commonswiki_p]>
It's supposed to provide web-accessible information, and it's not possible to wait 8 mins for completion. https://tools.wmflabs.org/sql-optimizer/%C2%A0 didn't offer any solution.
Any suggestions? Martin_______________________________________________ Wikimedia Cloud Services mailing list Cloud@lists.wikimedia.org (formerly labs-l@lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud