[Solved] Row for each date, or 31 columns?
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Row for each date, or 31 columns?

Currently I have a system whose front-end looks like this:

The following query:

SELECT      `l`.*, `s`.`month`, `s`.`year`
FROM        `summarylines` `l`
JOIN        `summaries` `s`
ON          `l`.`sumid` = `s`.`id`
ORDER BY    `l`.`sumid` ASC

Produces the following result:

I have a requirement to change the system to handle individual dates, rather than weeks. So on the front-end the columns would be each date, e.g. 2nd Jan, 6th Jan, 10th Jan, 12th Jan, 18th Jan, 20th Jan, 26th Jan, 27th Jan.

It is of course a major overhaul of both the back-end and the DB structure.

My question is: should I use a database with 31 columns for each possible day of the month? Any unused days would be NULL for that field (as it is now for any unused weeks). The positive in this approach is that I only need 1 row per item per month. E.g.

+--------+-------+--------+------+-----+------+------+-----+----+-----+------+-----+
| lineid | sumid | itemid |  1   |  2  |  3   |  4   |  5  | 6  |  7  |  8   | ... |
+--------+-------+--------+------+-----+------+------+-----+----+-----+------+-----+
|   1195 | 15123 |    165 | NULL |  66 | NULL | NULL |  72 | 88 |  95 | NULL | ... |
|   1196 | 15123 |    223 | NULL | 101 | NULL | NULL | 141 | 85 | 110 | NULL | ... |
+--------+-------+--------+------+-----+------+------+-----+----+-----+------+-----+

Or should I have a narrow table, with lots of rows? E.g.

+--------+-------+--------+-----+-----+
| lineid | sumid | itemid | day | qty |
+--------+-------+--------+-----+-----+
|   1195 | 15123 |    165 | 2   | 66  |
|   1196 | 15123 |    165 | 5   | 72  |
|   1197 | 15123 |    165 | 6   | 88  |
|   1198 | 15123 |    165 | 7   | 95  |
|   1199 | 15123 |    165 | ... | ... |
|   1200 | 15123 |    223 | 2   | 101 |
|   1201 | 15123 |    223 | 5   | 141 |
|   1202 | 15123 |    223 | 6   | 85  |
|   1203 | 15123 |    223 | 7   | 110 |
|   1204 | 15123 |    223 | ... | ... |
+--------+-------+--------+-----+-----+

A possible negative of this is that the back-end's SQL is currently coded in such a way that some of the existing code could just be altered if using a 31-col table. It would have to be completely rewritten using this approach.

FYI, most customers only have 1 or 2 deliveries per week, but most have 5-10 different items.

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `summaries` ADD INDEX `summaries_idx_id` (`id`);
ALTER TABLE `summarylines` ADD INDEX `summarylines_idx_sumid` (`sumid`);
The optimized query:
SELECT
        `l`.*,
        `s`.`month`,
        `s`.`year` 
    FROM
        `summarylines` `l` 
    JOIN
        `summaries` `s` 
            ON `l`.`sumid` = `s`.`id` 
    ORDER BY
        `l`.`sumid` ASC

Related Articles



* original question posted on StackOverflow here.