MySQL : DATE_FORMAT() vs. SUBSTR()
Since OpenDate is a datetime column, you don't need to use DATE_FORMAT(), you can simply GROUP BY DATE(OpenDate). And the extra CRC32() call seem completely redundant.

The query can be rewritten:

SELECT COUNT(*) AS TotalOpens, 
       DATE(OpenDate) AS DayOfWeek 
FROM stats_open 
WHERE ListID = 38 
  AND UserID = 4
GROUP BY DATE(OpenDate) 
ORDER BY TotalOpens DESC ;


Add an index on (ListID, UserID, OpenDate), to make it more efficient.