problem： 595. Big Countries
A country is big if it has an area of bigger than 3 million square km or a population of more than 25 million.
Write a SQL solution to output big countries' name, population and area.
Two obvious solutions:
#OR
SELECT name, population, area
FROM World
WHERE area > 3000000 OR population > 25000000
And Faster Union
#Union
SELECT name, population, area
FROM World
WHERE area > 3000000
UNION
SELECT name, population, area
FROM World
WHERE population > 25000000
Why Union is faster than OR?
Strictly speaking, Using UNION is faster when it comes to cases like scan two different column like this.
(Of course using UNION ALL is much faster than UNION since we don't need to sort the result. But it violates the requirements)
Suppose we are searching population and area, Given that MySQL usually uses one one index per table in a given query, so when it uses the 1st index rather than 2nd index, it would still have to do a table-scan to find rows that fit the 2nd index.
When using UNION, each sub-query can use the index of its search, then combine the sub-query by UNION.
I quote from a benchmark about UNION and OR, feel free to check it out:
Scenario 3: Selecting all columns for different fields
CPU Reads Duration Row Counts
OR 47 1278 443 1228
UNION 31 1334 400 1228
Scenario 4: Selecting Clustered index columns for different fields
CPU Reads Duration Row Counts
OR 0 319 366 1228
UNION 0 50 193 1228
Union not always faster than or!
Most good DBMSs use an internal query optimizer to combine the SELECT statements
before they are even processed. In theory, this means that from a performance
perspective, there should be no real difference between using multiple WHERE clause
conditions or a UNION. I say in theory, because, in practice, most query optimizers
don’t always do as good a job as they should. Your best bet is to test both methods to
see which will work best for you.
prob 181
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
description： find Employees Earning More Than Their Managers
ex：
+----------+
| Employee |
+----------+
| Joe |
+----------+
solution
# join is a little faster
select a.Name as Employee
from Employee a Join Employee b
on b.Id = a.ManagerId and a.Salary > b.Salary
select e.Name as Employee
from Employee e, Employee m
where e.ManagerId is not null and e.ManagerId = m.Id and e.Salary > m.Salary
把表中的性别f换为m m换成f
UPDATE salary SET sex = IF(sex='m','f','m');