Mysql – Set user variables from tables being queried in MySQL without locking

blockingMySQL

I have a data warehouse in AWS Aurora, with a pretty traditional Date Dimension (from a DateSK, gives your RelativeDays, Weeks, Months, etc).

First, I have a simple function to convert any date to the DateSK:

CREATE FUNCTION `toDateSK`(`myVarParam` DATE) RETURNS int(11)
BEGIN
    IF myVarParam IS NULL
    THEN SET myVarParam = NOW(); END IF;
    RETURN cast(date_format(myVarParam, '%Y%m%d') AS UNSIGNED);
END

I created a function to get the current date in RelativeDays for some date math. Function is:

CREATE FUNCTION `thisDay`() RETURNS int(11)
BEGIN
    SET @now := toDateSK(NOW());
    SET @res := (SELECT RelativeDays
                 FROM dimDate
                 WHERE DateSK = @now);
    RETURN @res;
END

I wanted to create a view:

CREATE OR REPLACE VIEW viewFirstActivity AS
SELECT
    da.AccountSK     AS AccountSK,
    min(fa.DateSK)    AS DateSK,
    min(dd.FullDate)  AS FullDate,
    min(dd.RelativeDays) AS RelativeDays
FROM ((factActivity fa JOIN dimAccount da ON ((fa.AccountSK = da.AccountSK))) JOIN dimDate dd ON ((fa.DateSK = dd.DateSK)))
WHERE (fa.ActivatedFlag = TRUE) AND dd.RelativeDays >= thisDay()-7
GROUP BY fa.AccountSK;

However, this causes tons of table waits if used in a way like this (I'm pulling this from memory):

SELECT * FROM viewFirstActivity WHERE AccountSK="12345" AND RelativeDays > thisDay()-3;

(I know this logically doesn't make sense, but I don't have the original query at my fingertips. The key point is the function was used both in the view, and in the select query. This seemed to create lots of locking, even though the dimDate table (InnoDB) was only being read).

If I reworked the query as follows (not using a view):

SET @var := thisDay()-3;
SELECT * FROM (  
    SELECT
        da.AccountSK     AS AccountSK,
        min(fa.DateSK)    AS DateSK,
        min(dd.FullDate)  AS FullDate,
        min(dd.RelativeDays) AS RelativeDays
    FROM ((factActivity fa JOIN dimAccount da ON ((fa.AccountSK = da.AccountSK))) JOIN dimDate dd ON ((fa.DateSK = dd.DateSK)))
    WHERE (fa.ActivatedFlag = TRUE) AND dd.RelativeDays >= thisDay()-7
    GROUP BY fa.AccountSK) AS tmp
WHERE AccountSK="12345" AND RelativeDays > @var;

This seems to remove the blocking, but now I can't use a convenient view.

I know I'm missing something basic here, but is it easy to explain what it is?

Best Answer

Your first function can be even simpler by noting that

SELECT 0 + DATE('2017-01-18 08:31:38'); -- 20170118

But that is not usable for arithmetic. Note that this time next month, 20170218, would be "100 days" from now:

For the second one, look into DATEDIFF(). (No need to convert from DATETIME to DATE.)

See also the TO_DAYS() and FROM_DAYS() functions. Today is 736712 and would work with aritmetic.

I suspect that all of this will eliminate the need for dimDate, thereby solving your stated problem?? If not sufficient, please provide SHOW CREATE TABLE for each table involved; we need to see what indexes you currently have.

Related Question