1

I'm working on a legacy web application which uses a MySQL (5.5.65-MariaDB) database which is hosted on a server running CentOS Linux 7.7.1908.

The application is a database of chemical substances and at the moment contains approx 270,000 substances. This number will grow over time but historically - over the last 3 years - only around 10,000 per year have been added.

Every substance has a unique ID and name which is stored in 1 table called substances. The structure is as follows:

> DESCRIBE substances;
+-------------+-----------------------+------+-----+---------+----------------+
| Field       | Type                  | Null | Key | Default | Extra          |
+-------------+-----------------------+------+-----+---------+----------------+
| id          | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | varchar(1500)         | NO   |     | NULL    |                |
+-------------+-----------------------+------+-----+---------+----------------+

So this table currently contains approx 270,000 rows: 1 per substance.

When a user views 1 individual substance in the application, it shows a table which is split into 2 columns. An example of this is as follows (I've removed the actual data since it's a proprietary application):

enter image description here

The rows on the left ("Regulatory List A ... C") are stored in a table called displays. The information in the rows on the right is stored in a table called display_substances. These tables have a 1:1 relationship: one displays.id relates to one display_substances.display_id. The structure of the tables is as follows:

> DESCRIBE displays;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(127)         | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+

> DESCRIBE display_substances;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| display_id   | smallint(5) unsigned  | NO   | MUL | NULL    |                |
| substance_id | mediumint(8) unsigned | NO   | MUL | NULL    |                |
| value        | text                  | NO   |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

On the screenshot, there are 2 rows which say "Not Listed ^". This is shown to the user when there is no display_substances.value for the substance (display_substances.substance_id) assigned to the Display (display_substances.display_id). In simple terms this means there is no data about that row stored in our database. So on the screenshot above there is only 1 display_substances.value - the one containing the "Lorem ipsum dolor..." text. The other 2 rows which have "Not Listed ^" do not have any rows in display_substances.

The query to generate this involves using an IFNULL ... GROUP_CONCAT condition which puts the text "Not Listed ^" in, if there is no corresponding row. The query is as follows:

-- ds.substance_id = 1 is an example (substances.id = 1)

SELECT 
d.id, 
IFNULL(
    (
        SELECT GROUP_CONCAT(value) FROM display_substances `ds` 
        WHERE `ds`.`display_id` = `d`.`id`
        AND ds.substance_id = 1 
        GROUP BY `ds`.`display_id`
    ), "Not Listed ^"
) `display_value` FROM displays `d` 
HAVING `display_value` = "Not Listed ^" -- This condition is explained further down 
ORDER BY d.id ASC

The problem

The displays table contains approx 890 rows, all of which are shown for every substance. Currently the display_substances table contains approx 1.8 million rows. However, this is because it does not contain rows for anything which is "Not Listed ^".

We have a project where we need to identify and log anything which is "Not Listed ^". The SQL given above generates exactly what we need (this is the purpose of the HAVING condition).

The SQL above executes in approx 1 second per substance. From some tests I've done on 100 substances, about 600 rows come back as "Not Listed ^" for any 1 individual substance.

This gives me 2 problems:

  1. 600 rows for 270,000 substances would result in 162,000,000 records to log.
  2. The time taken to execute this is 1 second * 270,000 substances which is approx 3 days (270k seconds).

The storage for the log - i.e. where we would INSERT rows to log this - is a table which is as follows:

+---------------------------+-------------+------+-----+---------+----------------+
| Field                     | Type        | Null | Key | Default | Extra          |
+---------------------------+-------------+------+-----+---------+----------------+
| id                        | int(11)     | NO   | PRI | NULL    | auto_increment |
| substance_id              | int(8)      | NO   |     | NULL    |                |
| display_id                | int(5)      | NO   |     | NULL    |                |
| created                   | datetime    | NO   |     | NULL    |                |
| modified                  | datetime    | NO   |     | NULL    |                |
+---------------------------+-------------+------+-----+---------+----------------+

I'm not convinced that I'm approaching this in the right way, and would appreciate advice on points (1) and (2) above.

Point (2) is of less concern because we would be able to run the process through crontab in the background and there's no time constraint of how quickly this needs to work.

The bigger concern is point (1) which is whether storing ~160 million rows in a table is really a good idea (bearing in mind our biggest table so far is 1.8 million rows). I anticipate the storage for that table would be at least 12 Gb. Is this excessive for a table, or is this normal in applications when you need to store such volumes of data?

How do people deal with this type of scenario?

3
  • Rewrite your query which uses correlated subquery, to LEFT JOIN, for displaying the output like on the screenstot. Use WHERE NOT EXISTS for selecting a list of "Not listed" rows. – Akina Jun 18 '20 at 10:51
  • 1
    Replace DESCRIBE (which is unusable) with CREATE TABLE for your tables (including all existing indexes). Create modelling data (3-10 rows per table is enough) and post this as INSERT INTO. Or create online fiddle with the above data. – Akina Jun 18 '20 at 10:53
  • 162M won't fit in MEDIUMINT; suggest INT UNSIGNED. – Rick James Jun 18 '20 at 22:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.