0

There is a table which are populated from an online service and updated every week.

This goes on a separate (second) database.

The table is like 300MB in size (1.5 million rows) processed by a cron that downloads the CSV data every week. This CSV file is 3GB in size (4 millions rows) where rows and columns are filtered to retain only a subset of the data. And the order of the columns may change. It has in the past. So we can't even predict based on the first row. Even row header names may change.

So its a total dump and filter by some hard-set row names.

In my Django's view which is a DRF powered one, my API URL takes 3 seconds (0:00:03.263031) to execute.

SELECT DISTINCT `Region` AS `Region`
FROM `my-table-name`
WHERE `flag` = 'condition'
LIMIT 0, 100

Thing is, if it just this endpoint that I'm fetching on the page it wouldn't kill. But there are many endpoints on the single page which are fetched when the user clicks on an INPUT element. And 3 seconds for each trigger is way too long.

What else can I do to optimize the table / query ?

enter image description here

3
  • What database system and version?
    – J.D.
    Mar 23, 2023 at 11:50
  • Server version: 8.0.32 - MySQL Community Server - GPL on localhost - eventually this would be on Azure VM with the latest version of 8 on Ubuntu 22.04.
    – anjanesh
    Mar 23, 2023 at 11:57
  • 3 seconds for 1.5M rows -- Not bad. Please provide SHOW CREATE TABLE.
    – Rick James
    Apr 5, 2023 at 20:14

1 Answer 1

2

You could try adding an index that covers your query like so:

CREATE INDEX IX_MyTableName_Flag_Region ON MyTableName (Flag, Region)

This index leads with the Flag column because it is a predicate in your WHERE clause. Then it follows with the Region field so that it's also included, since you're SELECTing that field.

4
  • 1
    Thanks - it got reduced to 0.80 seconds.
    – anjanesh
    Mar 23, 2023 at 12:16
  • @anjanesh No problem! Indexing is super helpful and important to understand. Reading through the docs I linked should be helpful in the future.
    – J.D.
    Mar 23, 2023 at 12:23
  • 1
    I have indexed one column alone in the past - didnt figure about indexing more than one column in a single index.
    – anjanesh
    Mar 23, 2023 at 13:14
  • 1
    @anjanesh Cool, yea, when it's more than one field in the index they call that a composite index or compound index sometimes.
    – J.D.
    Mar 23, 2023 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.