slow queries

  • Resolved wolfgang61

    (@wolfgang61)


    5 years, 8 months ago

    Trackserver 4.2.2.

    The following query takes about 13 seconds in avg, up to 60 seconds.

    Solution see at end of post.

    Count: 18  Time=13.13s (236s)  Lock=0.00s (0s)  Rows=0.9 (16), wordpress_7[wordpress_7]@localhost
  SELECT DISTINCT t.user_id, l.trip_id AS id FROM wp_ts_tracks t INNER JOIN wp_ts_locations l ON t.id = l.trip_id INNER JOIN (SELECT t2.user_id, MAX(l2.occurred) AS endts FROM wp_ts_locations l2 INNER JOIN wp_ts_tracks t2 ON l2.trip_id = t2.id GROUP BY t2.user_id) uu ON l.occurred = uu.endts AND t.user_id = uu.user_id WHERE t.user_id IN ('3')

    I never changed or added indexes, so on wp_ts_locations I have indexes on id,occurred,trip_id. And on wp_ts_tracks id and user_id.

    wp_ts_tracks holds 190 entries.
    wp_ts_locations holds 1313574 entries.

    There is only one user_id

    The delay comes from this subquery:
    SELECT t2.user_id, MAX(l2.occurred) AS endts FROM wp_ts_locations l2 INNER JOIN wp_ts_tracks t2 ON l2.trip_id = t2.id GROUP BY t2.user_id;

    First I had to do this because ‘0000-00-00 00:00:00’ was not accepted as default value:

    alter table wp_ts_locations alter occurred set default ‘1980-01-01’, alter created set default ‘1980-01-01’;

    Only then could I add this index which solves the problem:

    create index idx_trip_occ on wp_ts_locations (trip_id,occurred);

Viewing 7 replies - 1 through 7 (of 7 total)
  • Thread Starter wolfgang61

    (@wolfgang61)

    I forgot to mention that this query is executed by the plugin when displaying the life track.

    Plugin Author tinuzz

    (@tinuzz)

    Hi @wolfgang61,

    I have been working on Trackserver recently, and I would also like to include this change. I have a question however:

    which database (MySQL / MariaDB) en which version are you running?

    I have added the index on (trip_id,occurred) on my own server (MariaDB 10.1), and I did _not_ have to change the default value of the ‘occurred’ and ‘created’ columns, it accepts ‘0000-00-00 00:00:00’ just fine.

    Now I was just reading up on this topic, and it appears that MySQL from 5.7 by default does not accept ‘0000-00-00 00:00:00’, as it is infact an invalid date.

    https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date

    I started typing this message, because I wasn’t sure whether to include the code to update the default value, but after reading the MySQL manual, I think it would be best to do so.

    Thank you for your contribution. It will be included in the next version of Trackserver, which will hopefully be released within a few weeks.

    Best regards,
    Martijn.

    Thread Starter wolfgang61

    (@wolfgang61)

    Sorry for not having mentioned the mysql version – it is mysql 5.7

    
dpkg -l | grep mysql-server
ii  mysql-server                          5.7.27-0ubuntu0.18.04.1              all          MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.7                      5.7.27-0ubuntu0.18.04.1              amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-5.7                 5.7.27-0ubuntu0.18.04.1              amd64        MySQL database server binaries
    Plugin Author tinuzz

    (@tinuzz)

    Using TravisCI, I created a setup in which I can test any database update in a whole bunch of different MySQL and MariaDB versions. Doing his, I have asserted which queries work and are unlikely to cause problems. These database updates will be rolled out with the next version, which will be released as soon as possible. Thank you for your contribution.

    Best regards,
    Martijn.

    • This reply was modified 5 years, 6 months ago by tinuzz.
    Plugin Author tinuzz

    (@tinuzz)

    Version 4.3 was released Friday, containg the fix.

    Thread Starter wolfgang61

    (@wolfgang61)

    Now that I migrated to Debian 10, mariadb is used. Version 10.3. This one cannot make use of the additional index, the query seems to take forever. I stopped it after one hour!

    Can you get TravisCI to check for slow queries? Just to be sure future changes in mariadb/mysql/wptrackserver do not cause regressions.

    This variant speeds it up to two seconds on my system:

    
CREATE VIEW wp_ts_trip_ends AS SELECT t.user_id, MAX(l.occurred) AS endts, l.trip_id FROM wp_ts_locations l INNER JOIN wp_ts_tracks t ON l.trip_id = t.id GROUP BY l.trip_id;

SELECT DISTINCT user_id, trip_id FROM wp_ts_trip_ends e WHERE endts = (SELECT MAX(e2.endts) FROM wp_ts_trip_ends e2 WHERE e.user_id=e2.user_id);
    • This reply was modified 5 years, 2 months ago by wolfgang61.
    • This reply was modified 5 years, 2 months ago by wolfgang61.
    Thread Starter wolfgang61

    (@wolfgang61)

    and this is the patch for class-trackserver.php:

    
--- /mnt/snapshots/latest/var/lib/wordpress/rohdewald.de/plugins/trackserver/class-trackserver.php      2019-10-15 14:20:33.398915234 +0200
+++ class-trackserver.php       2020-01-16 12:38:47.209941098 +0100
@@ -3140,14 +3140,19 @@
 
                        $sql_in = "('" . implode( "','", $user_ids ) . "')";
 
-                       $sql = 'SELECT DISTINCT t.user_id, l.trip_id AS id FROM ' . $this->tbl_tracks . ' t INNER JOIN ' . $this->tbl_locations . ' l ' .
-                               'ON t.id = l.trip_id INNER JOIN (SELECT t2.user_id, MAX(l2.occurred) AS endts FROM ' . $this->tbl_locations . ' l2 ' .
-                               'INNER JOIN ' . $this->tbl_tracks . ' t2 ON l2.trip_id = t2.id GROUP BY t2.user_id) uu ON l.occurred = uu.endts ' .
-                               'AND t.user_id = uu.user_id WHERE t.user_id IN ' . $sql_in;
+                       $ends_view = $wpdb->prefix . 'ts_trip_ends';
+
+                       $sql= 'CREATE OR REPLACE VIEW ' . $ends_view . ' AS ' .
+                               'SELECT t.user_id, MAX(l.occurred) AS endts, l.trip_id FROM ' . $this->tbl_locations . ' l ' .
+                               'INNER JOIN ' . $this->tbl_tracks . ' t ON l.trip_id = t.id GROUP BY l.trip_id;';
+                       $res = $wpdb->get_results( $sql, OBJECT_K );
+
+                       $sql = 'SELECT DISTINCT user_id, trip_id AS id FROM ' . $ends_view . ' e ' .
+                               'WHERE endts = (SELECT MAX(e2.endts) FROM ' . $ends_view . ' e2 WHERE e.user_id=e2.user_id) AND user_id IN ' . $sql_in;
 
                        if ( $maxage > 0 ) {
                                $ts   = gmdate( 'Y-m-d H:i:s', ( time() + ( get_option( 'gmt_offset' ) * HOUR_IN_SECONDS ) - $maxage ) );
-                               $sql .= " AND uu.endts > '$ts'";
+                               $sql .= " AND endts > '$ts'";
                        }
 
                        $res       = $wpdb->get_results( $sql, OBJECT_K ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘slow queries’ is closed to new replies.