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?