Using index on complex query

1

Hi there! I need help with query performance - it very slow because huge amount of data. I don't know which index will be helpful.

I have table with few columns, but query used only this:

market_id INT
created_at TIMESTAMP
value DOUBLE

Query:

select market_id, MAX(value) max from tickers where created_at between '2016-12-22' and '2016-12-23' group by market_id

I tried index (market_id, created_at, value) - but it seems to be don't using by the query. Are there possibility to speedup the query by using index?

Thanks for your time!

2
  • make sure you filter on same datatype as the fields in the table
  • index on created_at would probably suffice
select market_id
      ,max(value) as [max]
  from tickers
 where created_at>=cast('2016-12-22' as datetime) /* change datetime to match datatype in table */
   and created_at< cast('2016-12-24' as datetime) /* change datetime to match datatype in table */
 group by market_id
;
1 Like
3

Thanks for the answer!

With CAST() function my script executes two times more faster. One question: CAST() allows only datetime, not timestamp - so if my column is timestamp type, that's bad?

And about INDEX by created_at column: as I understand first key in index need to be market_id, because of GROUP BY statement, isn't it?

4

On Microsoft SQL Server the timestamp is not a representation of date/time. It's actually used for row versioning and the content looks like a binary values.
So, which database engine are you using?

Regarding index, you need to understand how a Query is evaluated by the database engine. In your case:

  • from section
  • where section
  • group section
  • aggregate functions (max)

So I would say an index on created_at should do it :slight_smile:

5

Seems like you could do this in MySQL:

 where created_at>=timestamp '2016-12-22 00:00:00'
   and created_at< timestamp '2016-12-24 00:00:00'
1 Like
6

Thanks!

Maybe I need to use complex index, not only one field created_at?

7

You may try this, add a boolean column LAST and update last true for the market_id to false before insert now row for this market_id with LAST to true. Create an index on market_id and LAST. Query very simple:
just use;
where LAST = true

No more group by is needed.

1 Like
8

Good solution, thanks. I'll try this.
But the problem is this table very loaded by insert queries and I need to be very careful with data. Need check all cases - eq: new market_id in table, etc.

Thanks for your time!