So... what's the difference between say PHP, or Ruby, or other options when you're trying to develop the backend for a website/app (with a MySQL server)?

@nhgrif the way i look at it is what do you need the backend to do? If it's something like a single-user admin, I'd use python + django. If it's a RESTful backend for a JS frontend, I'd use php + restler. Best tool for the job and all that.

The backend is for something like a chat program... or maybe something like Twitter.

if it's like twitter you probably want a NoSQL solution

like a memory store; relational DB is gonna fall down there

probably same deal with a chat program

How does NoSQL compare to MySQL? I'm only familiar with SQL Server.

NoSQL is just a buzzword for not using SQL

or technically "not only sql"

stuff is mostly stored in a memory store and then maybe archived in a relational DB

e.g. cassandra, mongodb, etc

Cassandra/MongoDB are "NoSQL" solutions?

yes

same with redis, couchdb, and so on

maybe MySQL memory tables could qualify as NoSQL, not sure

What's the problem with using MySQL exactly?

just that you probably want an in-memory key-value store rather than a relational data store on disk

for what you're doing

you could probably wrangle MySQL into doing that

dev.mysql.com/doc/refman/5.6/en/innodb-memcached.html

MySQL vs NoSQL is like Haskell vs Java. They're different paradigms, not just different technologies.

NoSQL fits some problem much more naturally than a RDBMS does and the inverse.

^ that

Though I believe NoSQL is way overused.

it is, but twitter/chat kind of things are pretty good use cases IMO

Yeah, I certainly agree with that. I've just noticed a ton of bad use cases of it. I think there's a large group of people who think it's the next evolution of RDBMS, not a different option, so they automatically use it without understanding why they're using it.

@Corbin yeah, happens with any cool new(-ish) tech, people want to use it for everything

Yeah

just like node.js... there are cases where it's appropriate, but it's not really appropriate for the average web app

Well, that's why I'm asking the question. If a developer were already quite familiar with RDBMS, what's the downside of using it for this particular application, and does it benefit of using a new-to-the-developer technology here outweigh the drawback of using a new-to-the-developer technology?

it's just that if you're dealing with a lot of little ephemeral data that nobody's going to care about an hour after it's created, it's better to store it in memory so you can retrieve it fast for a while and then archive it later

In all honesty, unless you actually hit twitter scale, a RDBMS is going to be just fine.

Before I hit a Twitter scale, wouldn't someone just buy me out? ;)

i think with a chat app there would be a noticeable difference, esp. with MySQL

Hah. Hopefully. Then let them deal with it :p

In what way would the difference be noticable? That question still is completely unanswered.

Hrmmm, yeah I suppose a chat app does actually mean quite a ton of queries, although the tables will fit in memory for a long, long time, and indexing should be simple

@nhgrif slow vs fast, and also cost of maintaining server

@Corbin we're talking MySQL here, "should" does not always happen

Why would a chat app mean more queries then a twitter app if we assume the twitter posts are made at the same rate of speed as a chat app?

@DaggNabbit if facebook can bastardize MySQL into working for them at their scale, it can work for 99.99999% of people :p

they use MySQL?

Yup

that's nuts

I don't think they particularly want to use MySQL, but ya know... College project in 2006 basically automatically meant MySQL

the thing is, it's not just that the tables can fit in memory... you update it, you're writing to disk, enforcing ref. integrity, and so on

@nhgrif I suppose it wouldn't, actually. Just seems like it would at first thought. But, to have as many chat messages as tweets implies a lot more tweeters than chatters.

@DaggNabbit true, although referential integrity could technically be omitted... x.x

Why does it imply more tweeters? There's not a limit on how fast I can post tweets, is there?

Though some chats (like StackExchange) do have a limit at how frequently you can post chats.

look how many messages we're creating here right now, do twitter conversations happen that fast?

Yes, there probably is a limit on how frequently you can tweet.

And you're just being picky. You know chat messages are more frequent than tweets :p

In general yes, but I wasn't sure if there was something about chat messages that required more queries.

think of this chat room here as a big array of message objects. You say something, it gets pushed onto the array. It only needs to be stored to a database for archival purposes, maybe once every few minutes. And it can be stored on a separate server.

compare that to having to write to disk every time someone says something, and read from disk once for each person who sees it

Wow, as of a year ago, twitter had 50 reads for every write. Guess that makes sense though.

Yea, but here, there's a group of people in the room. This group of people can post messages, and everyone else in the group sees everyone else's messages (barring having someone ignored). I'm thinking of a scenario where the "room" isn't so clearly defined.

(I don't know if this is an argument for or against MySQL)

Have you ever played any sort of MMORPG?

not much but some of my friends play endlessly

so i'm familiar enough with them

I've wasted way more of my life on MMORPGs than I'd like to admit :p

So, in the chat for these games, there's usually different sorts of chats

A general chat, a guild chat maybe... but then there's also a say or yell chat usually

right, guild chat, area chat, etc

And these are more location based.

So, say chat goes to people within say 15 yards of me.

If someone says something and I'm 15 yards from them, I hear it

If I step 1 yard away and reply, they don't see my reply (but I do)

does that even need to be stored on disk at all?

then step 1 yard back, I can still talk to them in say chat

and if so, does it need to be anything more than a plaintext log for admins to possibly review later?

@DaggNabbit Yup. Gotta log chat so you can moderate harassment :/

yes but I'm not convinced it even needs to be stored in a relational way

What if the chat is more than just plain text?

Although hrmm... I suppose most MMORPGs woudl just go the plaintext route.

I can say that Lineage 2 certainly uses plain text logs.

What if you could "chat" an image?

or a video or an audio clip

do you actually chat an image, or a link to an image that gets displayed as an image?

Chat it. Like if you were on a mobile device, you push a button, the device's camera app opens, you snap a picture, and hit send.

Like snapchat.

ok, but say it uploads the image to a static content server, and then chats a link to that, and at the other end the client sees the link and shows an image

you wouldn't want to store the image itself in the database, so you wouldn't want to store it in the log either

The client shouldn't see the link, only the image...

And you might want the images for the same reason you'd want a log of chat.

the client sees the link, the person using the client sees the image

K

But if you're going to bother moderating the chat, why would you just let anyone get away with posting any sort of image?

you pretty much never want to store images in a DB

Then how do the images get logged for moderation purpose?

because admin's log reader client will show them links as images, just like user's chat client

log the link

store the image on static content server

Okay, so if I were going with a NoSQL solution

A message will have several parts

sender, channel... what else

1. The sender, 2. The text part of the message, 3. The data part of the message (a user could chat a picture with a caption all in one message), and 4. the location the message was sent from.

So... today's match is quite more exciting than the one yesterday...

Don't tell me Simon.

I'm watching it later.

Create a new chat room.

@nhgrif It's exciting, that's all I say :)

@nhgrif so the data part is actually a first-class message type, not just embedded in the message?

sort of like tumblr?

uhm, I don't know. Never tumblred.

With Twitter, a tweet has a pic link. That's not what the message would be. It'd be more like getting a picture in a text message to your phone

with tumblr you can post a plain text-type message, or an image-type message, or a video-type message, etc

@Simon - does my answer make sense yet?

Sure. But all the messages would have a plain-text part (potentially)

all of them can have text associated but there are a handful of top-level main content types

Yea, it'd be like that.

@rolfl I think it makes sense, yes. I haven't tried it out yet though. Will try it tomorrow.

@nhgrif yeah so it does have multiple fields, could be stored in a DB or just a plaintext log file, either way

But with a NoSQL solution, I'd be able to handle appropriately sending those messages?

i mean, if admins need to do complex queries like what percent of Bob's posts were images, then yeah, you'd want it in a DB

As well as listening for messages in which the message send location is within a particular distance of my location?

Yes, i think NoSQL would be good for sending/receiving them... for primary use

@Corbin codereview.stackexchange.com/questions/56162/…

and for admin review, either DB or plaintext log, depending on how much they need to be able to dig in and analyze

It's been so freakin' hot here today so I haven't done much today. I have no idea how I've apparently managed to get +170 rep...

We'd probably want a db for logging purposes, because might sell adds based on keywords, etc.

@nhgrif that would probably be smart. Just have it store stuff in the DB as often as you can afford to lose, basically... IOW if server crashes, do you care if you lose 1 minute of activity? Do you care if you lose an hour? Probably somewhere in between.

@nhgrif hrmm that's a rather interesting/awesome idea.Shame it's likely way over my head in terms of reviewing :p

Spoiler alert!

If you're talking about soccer, please take it to another room. I haven't watched it yet. :/

What's the difference between a store and a cache?

Everyone's watching it here!

But you're probably USA... at work, right?

Yeah. The match started at I think 3pm my time.

@nhgrif i don't think there is really a difference, maybe "cache" implies you got it from somewhere else and "store" implies it was generated internally

I get off at 4.

We have it on in the office (like all the games).

Be sure to turn your car radio off.... If that's a thing in america

In here you always get surprised by the radio if you don't want to hear something yet!

lol... they don't talk about soccer on the radio here...

Already thought so

When the USA played Germany, it was during lunch time. We watched that at work.

When USA played Belgium, I came in to work early and went home early.

en.wikipedia.org/wiki/NoSQL#Classification <-- The classification section.

I'm damn tired now though

Cache is implied to be data stored in a fast storage medium like memory which is a copy of data stored on a slower medium like disk/network. A store is a generic term for a place data is located.

TTGTB

Night @skiwi

@DanLyons that's a better description... so a cache is a store, but a store is not necessarily a cache bc it might be the primary copy

And I think I'll agree. TTGTB

ya - a cache is mostly about improving the locality of data which is frequently accessed

but in some cases, it could be an exact copy of the data stored elsewhere

@SimonAndréForsberg You'll get here before me I bet, damn tired as I said.

Further down that classification section, there's a table with the NoSQL db name, column with programming languages, and notes. Does the middle column indicate language you'd write an app in to use that NoSQL database?

The fun part about caches is that there are so many ways to do them wrong :P

any kind of cache i can think of is an exact copy of data stored somewhere else

Sometimes it's just a subset of the data which is frequently accessed, because the overall data set is too large for your cache storage

@nhgrif i think it's just languages with bindings for that particular thing

oh maybe not

i know there are CouchDB bindings for tons of languages

and MongoDB

you may just want to use MySQL with memcached on top if you're committed to MySQL

So, database aside, what's a good option for exposing the database to the different applications that would post or fetch messages

@nhgrif MySQL stored procedures are handy since all you have to do is pass parameters to them, regardless of the "passing" language

Results are consistent and maintenance is easy. Execution is fast.

@nhgrif if it's a web-based thing, you probably want a RESTful interface

the problem with web-based chat things is the web is all pull and no push

client can pull stuff from the server but server can't push it to the client

so maybe you want to look into websockets, or if it's not a web based thing, regular sockets