SQL Query taking forever is my query properly formatted?

Question

My server is a DigitalOcean VPS and I have 60G hd, 4G ram, 4G swap file and connecting through mysql workbench on a 1Gig internet connection. The below query takes over 30 min before I cancel it. Many queries take 1-2 minutes which I feel is too long for how simple the query is. This query may not be optimized. Is there a way to optimize the query? How can I optimize query speed?

(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 10 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 11 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 20 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 21 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 27 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 30 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 40 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 50 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 60 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 70 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 80 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 81 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 82 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 90 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 95 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 96 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 1 AND departmentNumber = 97 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 10 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 11 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 20 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 21 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 27 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 30 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 40 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 50 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 60 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 70 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 80 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 81 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 82 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 90 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 95 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 96 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 3 AND departmentNumber = 97 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 10 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 11 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 20 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 21 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 27 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 30 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 40 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 50 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 60 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 70 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 80 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 81 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 82 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 90 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 95 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 96 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 10 AND departmentNumber = 97 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 10 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 11 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 20 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 21 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 27 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 30 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 40 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 50 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 60 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 70 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 80 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 81 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 82 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 90 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 95 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 96 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 11 AND departmentNumber = 97 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 10 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 11 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 20 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 21 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 27 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 30 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 40 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 50 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 60 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 70 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 80 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 81 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 82 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 90 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 95 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 96 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 16 AND departmentNumber = 97 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 10 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 11 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 20 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 21 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 27 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 30 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 40 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 50 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 60 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 70 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 80 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 81 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 82 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 90 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 95 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 96 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 17 AND departmentNumber = 97 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 10 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 11 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 20 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 21 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 27 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 30 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 40 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 50 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 60 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 70 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 80 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 81 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 82 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 90 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 95 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 96 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 18 AND departmentNumber = 97 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 10 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 11 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 20 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 21 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 27 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 30 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 40 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 50 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 60 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 70 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 80 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 81 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 82 order by unitsSold desc limit 30) union all (select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 90 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 95 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 96 order by unitsSold desc limit 30) union all(select * from movement where saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day) AND StoreNumber = 19 AND departmentNumber = 97 order by unitsSold desc limit 30);

To summarize: There are nearly 300 of these being UNIONd together:

        SELECT  *
            from  movement
            where  saleDate = date_sub(curdate(), interval WEEKDAY(curdate()) day)
              AND  StoreNumber =       -- some number
              AND  departmentNumber =  -- some other number
            order by  unitsSold desc
            limit  30

score 2 · Accepted Answer · edited Apr 19 '17 at 17:18

My first comment is to add a composite INDEX(saleDate, StoreNumber, departmentNumber) -- in any order.

But, digging deeper, I see other issues.

Don't do SELECT *, instead do only SELECT id (assuming id is the PRIMARY KEY), then use the big union mess as a subquery to find any other columns you need. To make this work, then you need this covering index instead of the one I recommended above: INDEX(saleDate, StoreNumber, departmentNumber, unitsSold, id).

Question: That looks like a subquery; does the whole query is something like

SELECT *
        FROM ( that union mess ) AS u
    ORDER BY unitsSold DESC
    LIMIT 30

If so, that becomes the perfect time to change to

SELECT m.*
        FROM ( that union mess, but with only `id` ) AS u
        JOIN movement AS m  USING (id)
    ORDER BY m.unitsSold DESC
    LIMIT 30

But, even better is to do the LIMIT sooner:

SELECT m.*
    FROM ( SELECT id
            FROM ( that union mess, but with only `id` ) AS u
            ORDER BY unitsSold DESC
            LIMIT 30 )
    JOIN movement AS m  USING (id)
    ORDER BY unitsSold DESC

This version needs to fetch only 30 whole rows, not 278*30, as in the previous case.

Once you grok my suggestions, go back to the use of IN to see if it works well enough:

SELECT m.*
    FROM ( SELECT id
            FROM  movement
            WHERE  saleDate = CURDATE() - INTERVAL WEEKDAY(curdate()) DAY
              AND  StoreNumber      IN (...)
              AND  departmentNumber IN (...)
            order by  unitsSold desc
            limit  30 )
    JOIN movement AS m  USING (id)
    ORDER BY unitsSold DESC

Since is is hard to predict what index is best, I recommend multiple covering indexes for the Optimizer to choose among:

INDEX(saleDate, StoreNumber, departmentNumber, unitsSold, id)
INDEX(saleDate, unitsSold, departmentNumber, StoreNumber, id)

saleDate is first because it is the only =. id is last because it is not involved in the WHERE or ORDER BY, but simply there to be 'covering'. (See "Using index" in EXPLAIN.)

If you have a variant that involves a date range instead of a single saleDate, then all of what I said needs revising to optimize for it. Some of the principles will survive, but the indexes will not.

Proper indexing got my query down to 40 seconds. I could probably optimize further but just the indexing alone made it manageable. I am going to spend more time with your other suggestions, although I don't know that they will be an exact fit. Jasper reports requires sorting to be done in the query for proper display which is why the query is as repetitive as it is. Thanks for the help. — Aaron Martin, Mar 05 '17 at 19:52
Oops, my last two queries were missing a necessary `ORDER BY`. — Rick James, Mar 05 '17 at 21:32

score 0 · Answer 2 · edited May 23 '17 at 11:54

It looks like your query could be described as: top 30 rows by unitsSold for each StoreNumber & departmentNumber.

I'd write it this way:

SELECT t.*
FROM (
    SELECT m.*, 
      IF(@s=StoreNumber AND @d=departmentNumber, @r:=@r+1, @r:=1) AS rowNumber,
      @s:=StoreNumber AS StoreNumber, 
      @d:=departmentNumber AS departmentNumber
    FROM (@g:=0, @r:=0) AS _init
    CROSS JOIN movement AS m
    WHERE saleDate = CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY
    ORDER BY StoreNumber DESC, departmentNumber DESC, unitsSold DESC
) AS t
WHERE t.rowNumber <= 30;

Make sure you have an index on the table on columns:

ALTER TABLE movement ADD KEY (saleDate, StoreNumber, departmentNumber, unitsSold);

This is a pretty common pattern in MySQL, because MySQL doesn't support SQL windowing functions.

(note I have not tested the query above)

See also my answer to How to SELECT the newest four items per category?

SQL Query taking forever is my query properly formatted?

2 Answers2