New answers tagged union
The results from these views do not overlap and together cover 100% of the table. What keeps you from just querying the underlying table? Should be fastest: SELECT x.* FROM cases x JOIN case_clients cacl ON cacl.case_id = x.main_id WHERE cacl.client_id = 12046 ORDER BY x.sort_nr, x.id;
What you're asking the DB to do in Query one is: Give me ALL from table A FILTERED Give me ALL from table B FILTERED Give me ALL from table C FILTERED Give me ALL from table D FILTERED And then Union. In the second query you first get all the data, and only after that you do the join and the filter. JOIN and WHERE on a UNION query, which doesn't really ...
Since MySQL 5.6, MySQL may automatically convert IN-subqueries into a JOIN query. This is called semi-join transformation. By converting the subquery to a join, the MySQL optimizer may be able to process the tables in a different order than for traditional subquery execution. For your query, the amount of data that need to be accessed, will be much less ...
Don't use IN ( SELECT ... ); turn it into a JOIN for efficiency. What probably is happening is: New optimizations for IN ( SELECT ... ) failed to account for an old implementation of UNION. If there is no need for id on filesi and fileso, get rid of it and promote the UNIQUE key to being PRIMARY. More In older versions of MySQL, IN ( SELECT ... ) would ...
Top 50 recent answers are included