MySQL Forums
Forum List  »  Newbie

How to increase query execution time?
Posted by: Robert Feldman
Date: August 29, 2017 11:50AM

I am using an MySQL backend (with all tables) and an MS Access front end. I am running a query that joins two tables, one with about 1.1 million records and the other with about 17,000 records. The query tries to select about 100 distinct records (stop_id) from the larger table based on about 1100 records (trip_id) in the smaller table and then insert these values into a third table. There are about 100 records in the larger table for each of the 1100 trip_id values.

This is the query:

INSERT INTO tbl_stops_for_routes (stop_id, direction) SELECT DISTINCT tbl_gtfs_stop_times.stop_id, tbl_gtfs_trips.direction_id FROM tbl_gtfs_trips INNER JOIN tbl_gtfs_stop_times ON tbl_gtfs_trips.trip_id = tbl_gtfs_stop_times.trip_id WHERE tbl_gtfs_trips.route_id = "3";

The query aborts after about 40 seconds with the following error:

ODBC--call failed.
[MySQL][ODBC 5.3(w) Driver][mysqld-5.7.19-log]Query execution was interrupted, maximum statement execution time exceeded (#3024)

The query succeeds when the number of trip_id records is smaller, such as 100.

My question is: how can I increase the amount of time the query will continue to execute before it times out?

Options: ReplyQuote


Subject
Written By
Posted
How to increase query execution time?
August 29, 2017 11:50AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.