News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Question about database (SMFs)

Started by Jade Elizabeth, December 02, 2014, 03:33:53 AM

Previous topic - Next topic

Jade Elizabeth

I'm just curious...On the SMF topics table it has fields for:
id_first_msg
id_last_msg
id_member_started
id_member_updated

I'm just wondering why it needs them? Couldn't it just figure it out with a join? :)
Once proud Documentation Writer and Help Squad Leader | Check out my new adult coloring career: Color With Jade/Patreon.

margarett

It could. But not doing it prevents one or several JOIN (s) to the biggest table in SMF (messages). And massively increasing performance while at it :)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Arantor

Actually, those are specifically there so joins can even be done.

You can only do the type of join you're thinking of when you start involving subselects.

Jade Elizabeth

Would you guys mind expanding?

I'm trying to figure out what I would want if I were designing a database for a forum myself and I'd really like to understand the reason these are there :).
Once proud Documentation Writer and Help Squad Leader | Check out my new adult coloring career: Color With Jade/Patreon.

Arantor

When you're doing a join, you're telling MySQL (or whatever RDBMS) how to join the tables.

Whatever the circumstances, you're telling MySQL to join from table A to table B. The more specific the criteria, the better because the less work you have to do in getting there.

Take SMF's case, for example. We have topics and messages, and there's a natural relationship there (one topic:many messages), which is the heart of how RDBMSes like to work.

Now, when we're finding a list of topics, we want to know details from the first post and the last post. Now, sure, we can go away and do a join from the topics table to (everything in the messages table), or from the topics table to (everything in the messages table where id_topic matches), but in a normal join, you end up pulling ALL the matching rows because that's what the database does. It fetches every row that matches criteria.

Now, for that, you have two choices. You can either pull all the rows (which means for a topic, fetching all the messages in the topic) or you can do a subselect (which SMF prevents for security reasons, though I believe 2.1 relaxes that), whereby you end up saying to select from the topic and then to have a second query embedded in the first to fetch the first or last message as you need it.

This is, of course, not an especially difficult task when you have indexes to do stuff but compared to just tracking the first/last message it's a lot of work.

So we store the first/last message ids, we can do a join directly to the only rows we want, and it's the fastest type of join since it is using the index that is already there to get exactly and only what we want. No unnecessary disk loading, no unnecessary memory use or anything.

Jade Elizabeth

Okay that makes complete sense. I thought it would add to it which is why I was a bit hesitant about it but now I see what you mean. Does it add much to the queries when they're responding to topics? Does it make that more difficult or load longer on the server?
Once proud Documentation Writer and Help Squad Leader | Check out my new adult coloring career: Color With Jade/Patreon.

Arantor

More joins = more work, simple as that.

Joins done properly making proper use of keys will always be better than joins done badly, though.

Jade Elizabeth

Once proud Documentation Writer and Help Squad Leader | Check out my new adult coloring career: Color With Jade/Patreon.

Advertisement: