CODEWITHSUNDEEP
mysqlsqlmysql-workbenchinnodb
mikebvs
mikebvs

Reputation: 111

MySQL queries are timing out on MySQL Workbench

InnoDB MySQL 5.7

Hey everyone, as the title states, the queries I've been using are timing out on MySQL Workbench. I'm using the same queries as I was a week ago, however instead of taking 0.5s, they are taking 15-25s.. An example is the following:

SELECT * FROM <table> ORDER BY <time column> DESC;

On another note, I'm using some fairly lengthy MySQL queries in order to gather some rough statistics from the data, these I expect to take longer, but I'm unsure if there is a better way to go about it. I should also mention that I only currently have SELECT/SHOW permissions.. Here is the lengthier query that is timing out:

SELECT DATE(<time_sent_column>) as Update_Date, 
ABS(AVG(UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>))) AS AVG_Latency,
AVG((SELECT 
    ABS(AVG(UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>))) 
    FROM <table_name> 
    WHERE ((UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>) < 10800))))
AS Total_Average_Latency,
STDDEV(UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>)) as STDDEV_Latency,
AVG((SELECT 
    ABS(STDDEV(UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>))) 
    FROM <table_name> 
    WHERE ((UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>) < 10800))))
AS Total_STDDEV_Latency,
VAR_SAMP(UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(create_date)) as Variance_Latency,
AVG((SELECT 
    ABS(VAR_SAMP(UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>))) 
    FROM <table_name>
    WHERE ((UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>) < 10800))))
AS Average_Variance_Latency,
MIN(ABS((UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>)))) AS Min_Latency,
MAX(ABS((UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>)))) AS Max_Latency
FROM <table_name>
WHERE ((ABS(UNIX_TIMESTAMP(<time_created_column>) - UNIX_TIMESTAMP(<time_sent_column>)) < 10800) AND created_at > '2018-05-01')
GROUP BY DATE(<time_created_column>)
ORDER BY DATE(<time_created_column>) DESC;

I could break all these up, however I would prefer to have them in a single table after the query if possible.. I'm pretty new to MySQL and so I'm not sure if I'm able to use JOINs or anything of that sort with only SELECT/SHOW permissions..

The database is at 50% of it's connections, minimal traffic, 98.7% key efficiency, 87.5% InnoDB Buffer Usage, 0 reads/writes per sec.. Any advice would be great! Thanks!

Upvotes: 0

Views: 1509

Answers (1)

Rick James
Rick James

Reputation: 142503

(In the below, I assume that time_created is less than time_sent; adjust the code if that is not a given.)

I would start with

CREATE TEMPORARY TABLE t
    SELECT DATE(time_sent)    AS Update_Date,
           DATE(time_created) AS Created_Date,
           UNIX_TIMESTAMP(time_sent) - UNIX_TIMESTAMP(time_created)) AS Latency
        FROM table_name
        WHERE UNIX_TIMESTAMP(time_sent) - UNIX_TIMESTAMP(time_created)) < 10800
          AND created_at > '2018-05-01';

and then build the query from that temp table. The next query will need a lot fewer keystrokes. And it won't need any subqueries.

(I assume create_date was a typo?)

(Perhaps you want >= in created_at > '2018-05-01';?)

Note that the test for < 10800 is probably redundant in all expressions other than the WHERE.

Upvotes: 0

Related Questions