Is there any alternative for using *OR* operator in mysql?-CodePudding

On my server hundreds of thousands records exist. to find some of the records I have used OR operator in mysql. While using OR operator I am facing a query performance issue.

SELECT * FROM abc_table AS a 
LEFT JOIN def_table AS b 
where (a.abc_column = "O" OR (a.abc_column IS NULL AND b.name = "XYZ"))

and in where many conditions will be added later.

CodePudding user response：

Do you use index for both tables?

EX: abc_table: idx_abc(abc_column) def_table: idx_name(name)

If you have them, it could eliminate most data before join two tables. You should provide the ddl of tables and the explain plan of query for more details.

CodePudding user response：

You can create a view of each criteria that you want to be apply separately then join the views. another important point is this: we do not have same column in these two table that the join be done successfully.