Menu
Just a small question regarding joins. I have a table with around 30 fields and I was thinking about making a second table to store 10 of those fields. Then I would just join them in with the main data. The 10 fields that I was planning to store in a second table does not get queried directly, it's just some settings for the data in the first table.
Something like:
Table 1
Id
Data1
Data2
Data3
etc ...
Table 2
Id (same id as table one)
Settings1
Settings2
Settings3
Is this a bad solution? Should I just use one table? How much performance impact does it have? All entries in table 1 would also then have an entry in table 2.
Small update is in order. Most of the Data fields are of the type varchar and 2 of them are of the type text. How is indexing treated? My plan is to index 2 data fields, email (varchar 50) and author (varchar 20). And yes, all records in Table 1 will have a record in Table 2. Most of the settings fields are of the bit type, around 80%. The rest is a mix between int and varchar. The varchars can be null.
818
Joins are slow, avoid them if possible. You cannot avoid joins in all cases, joins are necessary for some tasks. If you want help with some query optimizing, please provide more details. Everything matters: query, data, indexes, plan, etc.
You can replace the JOIN keyword with a comma in the FROM clause. What do you do next? There's no ON keyword for you to state the joining condition as there would be when using JOIN , e.g., on which two columns you want to join the tables. In this method, you simply use a WHERE clause to do so.
Using the SQL JOIN clause is necessary if you want to query multiple tables. Sooner or later, you'll have to use more than one table in a query. It's the nature of relational databases in general – they consist of data that's usually saved in multiple tables; in turn, these form a database.
Basically, join order DOES matter because if we can join two tables that will reduce the number of rows needed to be processed by subsequent steps, then our performance will improve.
This is known as vertical partitioning, and is a legitimate strategy. You might do this for the following reasons:
There will likely be a small performance hit when accessing through the JOIN, but performance may increase in the cases where you only need to access one or the other of the component tables.
55
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
