Mahdi: Wrksx: yes, because timezone changes 2 times per year

Overall: Rumery, okay, but is there anything you can think of that would be useful to quicken the queries?

Schultze: Considering that’s all I can think of storing

Rumery: Delzer, you need to know which queries – then you can create proper indexes, there are no “right” indexes etc without queries to use them :

Dripps: I was minorly considering an AUTO_INCREMENT where the app generating the graph would store the last AUTO_INCREMENT and do a where idlastId

Furutani: Boleware, is there a way to refer to CST/CDT ? I saw CT everywhere but it actually doesn’t exist in mysql

Duewall: But I don’t know if I might go overboard considering I’d be inserting every second

Rumery: Delzer, if you query a time series for specific server then it will need a different index than querying one specific time range for all servers

Lowden: Oh wait, i’d just use the timestamp facepalm

Zillman: Wrksx: you could use offset

Rumery: Is timestamp unique accross different inserts? or might two rows happen to have the same timestamp?

Clyatt: Delzer: What if there are 100 rows with same timestamp?

Furutani: Boleware, I think I misexplained it. Anyway I’ll stick to use a city name, if that’s the standard way to do.

Clyatt: Delzer: If by “go overboard” you mean you are concerned you will overflow the auto_inc you are wrong

Warner: Then that’s that. But a server_session_id will never have the same timestamp considering that server is meant to insert data every 1000-2000 ms

Betcher: Salle, hehe alright then

Rumery: Delzer, combination of timestamp and session might actually be unique in some use cases, timestamp by itself will very probably have occasional duplicates

Clyatt: Delzer: Make it BIGINT UNSIGNED and you will need thousands of years

Rzucidlo: Yeah. I guess I can PK the session and timestamp.

Clyatt: Delzer: Max value for BIGINT UNSIGNED is 18446744073709551615 :

Furutani: Yeah unsigned bigint is virtually infinite

Rumery: Delzer, now you need to decide which goes first into the PK – which one will be more useful. but that again depends on actual queries and use cases

Tuchy: Rumery, wait what, that matters? O.o

Furutani: I was the same when I discovered this =

Rumery: Delzer, yes, order of columns in a composite index matters :

Clyatt: Delzer: Even if you insert 1 000 000 rows per second which is unlikely with currently existing hardware that still means more than 500 000 years are required

Furutani: But actually it’s very nice that the order matter

Decoux: Salle: well, account for gaps

Rumery: Decoux, there are ways to burn a lot of auto_increment values, but usually those do not go to millions per second I think :

Clyatt: Decoux: With 1m rows per sec the max biging unsigned will be reached in about 580 000 years with no gaps

Levens: Well, now that I think about it, I think the session id might be more important.

Whichard: Salle r/theydidthemath 😛

Rumery: Delzer, for fastest inserts the timestamp might be better first – but I suppose you won’t get over 1k insert per second anytime soon

Decoux: Rumery: I’ve seen ugly cases

Clyatt: Delzer: Besides all this fun Rumery told yu the most important thing: Add indexes only when you know they will help certain queries

Rumery: Decoux, that ugly? I just hope I will never meet them

Sailor: Mhm, prior to this talk, I never had any indexes, so its either this or nothing really. :

Bullen: I’m ***uming that is very valid mentality, right?

Padol: Indexing something instead of nothing?

Decoux: Rumery: a single app retried 1000 times if the transaction failed.

Decoux: Delzer: you never had *any* indexes?