-1

I have the following query in django:

            end_date = timezone.now()
            start_date = end_date + timedelta(-5*365)
            queryset = (
                            DailyPriceHistory
                            .objects
                            .filter(symbol=symbol,datetime_utc__range=(start_date, end_date))
                            .order_by('datetime')
                            .order_by('creation_time')
                            .values_list('high','low','open','datetime','close','creation_time')
                        )

the Sql it generates is

SELECT `daily_price_history`.`high`,
       `daily_price_history`.`low`,
       `daily_price_history`.`open`,
       `daily_price_history`.`datetime`,
       `daily_price_history`.`close`,
       `daily_price_history`.`creation_time`
FROM `daily_price_history`
WHERE (`daily_price_history`.`datetime_utc`
        BETWEEN '2015-12-04 18:43:28.710229'
            AND '2020-12-02 18:43:28.710229'
        AND `daily_price_history`.`symbol` = 'A')
ORDER BY `daily_price_history`.`creation_time` ASC

Currently i have symbol and datatime as seperately indexed columns

I found why the filter sequence is not followed. i.e .filter(symbol=symbol,datetime_utc__range=(start_date, end_date))

I wanted

WHERE (`daily_price_history`.`symbol` = 'A'
   AND `daily_price_history`.`datetime_utc`
      BETWEEN '2015-12-04 18:43:28.710229'
          AND '2020-12-02 18:43:28.710229')

but it uses

WHERE (`daily_price_history`.`datetime_utc`
           BETWEEN '2015-12-04 18:43:28.710229'
               AND '2020-12-02 18:43:28.710229'
           AND `daily_price_history`.`symbol` = 'A')

Also i dont see order_by('datetime') in the sql, is this because datetime is indexed

Rick James
  • 135,179
  • 13
  • 127
  • 222
Santhosh
  • 9,965
  • 20
  • 103
  • 243
  • Your question is not focused as you have multiple questions in it. To sum it up: Why do you think order of statement matter, also what do you think using two times order_by should produce https://docs.djangoproject.com/en/3.1/ref/models/querysets/#django.db.models.query.QuerySet.order_by – iklinac Dec 02 '20 at 19:54

1 Answers1

0
WHERE (foo) AND (bar)

optimizes the same as

WHERE (bar) AND (foo)

However, these are useful in different situations:

INDEX(a,b)
INDEX(b,a)

You need

INDEX(symbol, datetime)

regardless of the order in the WHERE.

But this is efficient because

WHERE symbol = '...'        -- Equality test (put first in index)
  AND datetime BETWEEN ...  -- range test (put last in index)

This may not be performed efficiently, regardless of the index:

WHERE symbol IN (...)       -- IN() is harder than = to optimize
  AND datatime BETWEEN ...
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • can you also see a similar question i have asked, `https://stackoverflow.com/questions/65150483/mysql-how-to-speed-up-an-sql-query-for-getting-data`. Here i am using where with `IN` and `greater than`. SO in this case also will index does not have any efficiency – Santhosh Dec 05 '20 at 03:54
  • OK, this Q&A talks about `=`, that one talks about `IN`. Notice how there can be big differences in the Answer for seemingly small differences in the `WHERE`. Seems like I answered the `WHERE id > ...` in yet another Q&A. The answer involving `PARTITION` may be the best for all of them. – Rick James Dec 05 '20 at 16:34