CODEWITHSUNDEEP
mysqlselectpresto
Alex
Alex

Reputation: 1167

SQL Presto - How to join multiple schemea from select query

Let say there are 3 seperately schemeas in the tables such (Year, Month, Day) that they will be used in the select from query. Is there possible way to optimse below select query as not as hard-coding? For example, I am looking for anyway that we can join the schemeas first then used as one combine schemea for the select query (idea such concat(year,month,day) = CAST(2022-10-03 as date))?

Not optimse verson
select * 
FROM testing_table where Year = 2022 AND Month = 10 AND Day = 15

Tables
|Year    | Month  |Day |
| ----   | ------ | -- |
| 2022   | 10     | 15 |

Upvotes: 0

Views: 320

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562641

Yes, you can concatenate the three columns (not schemas) into one date value.

But if you do that, it will actually prevent optimization.

Putting the columns into a function call like CONCAT() will spoil the use of a conventional index, which is the most common form of optimization. It will hurt performance because it will require the query to do a table-scan (examine all rows in the table). Whereas if you have an index on the three columns, the query can use it to examine only the matching rows.

You could also write the query using tuple comparison, and this will use an index on (Year, Month, Day) in MySQL 5.6 or later:

select * 
FROM testing_table where (Year, Month, Day) = (2022, 10, 15)

You can also use an expression index:

alter table testing_table add index on
  ((str_to_date(concat(year, '-', month, '-', day), '%Y-%m-%d')));

Then you can make use of the index by using the same expression:

mysql> explain select * from testing_table where str_to_date(concat(year, '-', month, '-', day), '%Y-%m-%d') = '2022-10-15'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: testing_table
   partitions: NULL
         type: ref
possible_keys: functional_index
          key: functional_index
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

Upvotes: 1

Related Questions