My real question is there in whatever way to higher manage this database implementation.
Presuming the next table (simplified for that discussion):
user_id (PK) user_name
thread_id (PK) user_id (Index) thread_title
I would like a view with a listing from the thread game titles along with the user posting them.
So this is my usual query:
SELECT thread_title, thread_table.user_id, user_name FROM thread_table INNER JOIN user_table WHERE user_table.user_id = thread_table.user_id
My real question is, could be this the best way (faster) to obtain the user_title getting the consumeridentification?
This can be a simplified example, however i convey more complex queries, joining different tables and it is “painful” to need to join another table only to obtain the user_title.
What exactly could I actually do to enhance the potency of this execution? A minimum of must i create a memory table of user_table having a hash answer to better obtain the user_title getting the consumeridentification?
This is actually the correct implementation. If JOINs are painful for you, then you're while using wrong tool! JOINs are an natural a part of SQL, which fact you need to make use of a JOIN to retrieve a particular bit of information does not necessarily mean that you ought to improve your schema.
If this sounds like a typical pattern that you employ again and again in other queries, then its an ideal candidate for any view. You'll be able to use that view in other queries, and eliminate some typing.
If performance can be a concern, you can begin to consider denomralization, but I'm not obtaining the sense that you're at that point here. Posting your query occasions and also the creation of
EXPLAIN PLAN is needed identify this.