0

I have a table let us called table_A. This table has a field let us called Entity. This field could have either zero, one, or two values for each row. Min is zero, max is two, and most rows have either one or two entities. The expected row number is more than 10M.

Design 1:

table_A:

id Entity_1 Entity_2 otherColumns
1 Data1 Data2 ...
2 Data1 NULL ...
3 NULL NULL ...
SELECT 
    *
FROM 
    table_A
WHERE
    Entity_1='Data1' OR Entity_2='Data1'
    #OR Entity_1 in ('Data1','Data2') OR Entity_2 in ('Data1','Data2')

Design 2:

table_A:

id otherColumns
1 ...
2 ...
3 ...

table_B:

id table_A_id Entity
1 1 Data1
2 1 Data2
3 2 Data1
SELECT 
    *
FROM 
    table_A INNER JOIN table_B ON table_A.id=table_B.table_A_id;
WHERE
    Entity='Data1'
    #OR Entity in ('Data1','Data2')

My question is, which database structure and query will be more efficient for inserting, updating, deleting, and selecting? If, there are better solutions, please consider them.

2
  • 1
    Probably depends on the length of your OR list? Run a plan for both with your own data! For readability, the JOIN is probably better... As with many things IT, the answer is it depends.
    – Vérace
    Oct 1, 2023 at 13:02
  • 1
    It also depends on what version of MySQL. In the old days, OR and/or IN led to not using the index -- slow. In the latest version, some cases are better optimized.
    – Rick James
    Oct 1, 2023 at 14:41

2 Answers 2

1

You should design tables around the objects they represent and the use cases of those objects. Then performance tune after, especially since I'm about to make the same performance improvement recommendation that applies to both design options you've listed. Additionally, with performance tuning, it's partly guesswork up until you actually run the queries and compare their execution plans via EXPLAIN ANALYZE as it depends on other factors you haven't provided here such as the indexes on the tables, and quantitative properties of your particular data.

To answer your question directly:

Which one is better, the OR operation or JOINING two tables in MySQL?

The OR operator is known to be difficult for database engines to optimize for when generating a query plan. It is not a Sargable operator, which means it can't efficiently use an index to seek to the data you're looking for, typically. In both example queries you're using OR in the WHERE clause. You can potentially improve both by re-writing those queries (regardless of which table design you choose) by using a UNION ALL clause to traverse the index of the table twice, efficiently with a seek, like so:

SELECT ...
FROM ...
[JOIN ... ON ...] -- Optional, dependent on your table design choice
WHERE Entity = 'Data1' -- Seek on the index for this value 

UNION ALL -- Combine the two results together akin to an `OR` operation 

SELECT ...
FROM ...
[JOIN ... ON ...] -- Optional, dependent on your table design choice
WHERE Entity = 'Data2' -- And seek on the index for this value 

That answers your question on performance in regards to SELECTing. But in regards to DML operations as per this question:

My question is, which database structure and query will be more efficient for inserting, updating, deleting...

That is one that can potentially be improved by splitting up the data in two tables by design, if it makes logical sense to do so based on the use cases of the system.

The reasoning having multiple tables could improve performance for write operations, is because of concurrency. If the data in one table changes at a different rate than the data in the other table, then you can theoretically write the changes of the data, concurrently, more efficiently. Or make smaller data changes at a time, since it's a less wide row, such as on INSERT.

If you're using pessimistic concurrency for some reason, then this would allow you to SELECT from one table while the other table is concurrently being written to, as well. So if your use cases require locking to safely process, having the data (when thought out well) split across multiple tables may make sense, to improve concurrency. But this should come naturally, again, as I mentioned in my initial paragraph about designing your tables to the objects they represent and the use cases of those objects.

2
  • Thanks for your answer, but what if I have more than two entities? Does UNION ALL perform better than IN?
    – Niyaz
    Oct 2, 2023 at 8:30
  • 1
    @Niyaz Yes, the longer the list of ORs or IN clause, the more likely UNION ALL results in a more performant query plan instead, generally.
    – J.D.
    Oct 2, 2023 at 11:52
1

Design 2 is likely to be faster, at least for some cases. But let's be clear about the PK, etc:

CREATE TABLE B (
    A_id...,
    Entity ...,
    PRIMARY KEY(Entity, A_id),
    INDEX(A_id, Entity)
) ENGINE=InnoDB;

There is no need for id since you have (and even 'need') to state that the pair (Entity, A_id) is Unique. Also, this provides the optimal lookup for FROM B WHERE Entity = '...'.

UNION ALL is definitely beneficial for complex ORs, but perhaps your simple case of IN does not need that.

SELECT A.*
    FROM A
    JOIN B  ON B.A_id = A.id
    WHERE B.Entity = ... // IN (...)

(Don't use OR in this case.)

That SELECT will fetch the few rows needed from B, then reach into A only the few times. But... If both 1 and 2 are in B, then IN (1,2) will fetch two rows. So, let's use DISTINCT to avoid dups:

SELECT A.*
    FROM ( SELECT DISTINCT A_id
              FROM B WHERE B.Entity ... ) AS x
    JOIN A  ON A.id = x.A_id;

(Another formulation would involve EXISTS.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.