Avoid full table scan in MySQL

Stats

SHOW GLOBAL STATUS LIKE 'Select%';

Counter Select_scan shows how many full table scans were done since last MySQL restart.

Counter Select_full_join is even worse as MySQL has to perform a full table scan against a joined table which is even slower.

With that being said, we need to try the best to avoid full table scan when writing queries. 

Use index

Apart from PK and foreign keys, add index to columns

• Columns frequently used to join tables
• Columns that are frequently used as conditions in a query
• Columns that have a high percentage of unique values

Without index on the column appears in where clause or sort by, MySQL will walk through the entire table to filter rows one-by-one.

Best practice

Avoid using function or math

SELECT * FROM table WHERE func(a) = 100
SELECT * FROM table WHERE a + 3 < 100

Avoid using Not equal and NOT IN

SELECT * FROM table WHERE a <> 1
SELECT * FROM table WHERE a NOT IN (1,2,3)

Avoid Bitwise on numeric column

SELECT * FROM table WHERE (a & 4) = 0

Avoid putting a wild-card before the first characters of the search criteria

SELECT * FROM table WHERE a LIKE '%abc'

Avoid the OR Operator

SELECT * FROM table WHERE a = 1 OR a = 2 OR a = 3

Try to replace it with an IN operator, something like SELECT * FROM table WHERE a IN (1,2,3)

Avoid using Having

Avoid using Order by if possible

Avoid using Group by if possible

Avoid using DISTINCT if possible

Avoid using ORDER BY RAND()

Avoid SELECT COUNT(*) FROM table

InnoDB doing a full table scan for this statement.

读书笔记 - ZERO to ONE

Peter Thiel's book is about notes on startups, or how to build the future.

The challenge of the future
Question received ideas and rethink business from scratch
What important truth do very few people agree with you on?
Most people think the future of the world will be defined by globalization, but the truth is that technology matters more.

Party like it's 1999
make incremental advances
stay lean and flexible
improve on the competition
focus on product, not sales

All happy companies are different
if you want to create and capture lasting value, don't build an undifferentiated commodity business.
in business, money is either an important thing or it is everything.
Monopolists can afford to think about things other than making money.
monopoly is the condition of every successful business.

The ideology of competition
Creative monopoly means new products that benefit everybody and sustainable profits for the creator.

Last mover advantage
Escaping competition will give you a monopoly, but even monopoly is only a great business if it can endure in the future.
For a company to be valuable, it must grow and endure, but many entrepreneurs focus only on short-term growth.
Will this business still be around a decade from now?

Characteristics of monopoly
1. proprietary technology
2. network effects
3. economics of scale
4. branding

Building a monopoly
1. start small and monopolize (every startup should start with a very small market)
2. scaling up
3. don't disrupt (avoid competition as much as possible)
4. that last will be the first (you must study the endgame before everything else)

You are not a lottery ticket
The most contentious question in business is whether success comes from luck or skill.
Can you control your future? Indefinite pessimism, definite pessimism, definite optimism, indefinite optimism
Our indefinitely optimistic world: indefinite finance, indefinite politics, indefinite philosophy, indefinite life
Is indefinite optimism even possible?
A startup is the largest endeavor over which you can have definite mastery. You can have agency not just over your own life, but over a small and important part of the world.

Follow the money
Money makes money.
We don't live in a normal world, we live under a power law.
Venture capitalists (VCs) aim to identify, fund and profit from promising early-stage companies.
The biggest secret in venture capital is that the best investment in a successful fund equals or outperforms the entire rest of fund combined.
every single company in a good venture portfolio must have the potential to succeed at vast scale.
why people don't see the power law?
If you do start your own company, you must remember the power law to operate it well.
The most important things are singular: one market will probably be better than all others, one distribution strategy usually dominates all others.
In a power law world, you can't afford not to think hard about where your actions will fall on the curve.

Secrets
Every one of today's most famous and familiar ideas was once unknown and unsuspected.
What important truth do very few people agree with you on?
What valuable company is nobody building?
How to find secrets?
The best place to look for secrets is where no one else is looking.
Unless you have perfectly conventional beliefs, it's rarely a good idea to tell everybody everything that you know.

Foundations
Every great company is unique, but there are a few things that every business must get right at the beginning.
Thiel's law: a startup messed up at its foundation cannot be fixed.
When I consider investing in a startup, I study the founding teams. Technical abilities and complementary skill sets matter, but how well the founders know each other and how well they work together matter just as much.
ownership, possession, and control - it is not just founders who need to get along. Everyone in your company needs to work well together.
Cash is not king. Any kind of cash is more about the present than the future.

The mechanics of Mafia
Every company is a culture. A startup is a team of people on a mission, and a good culture is just what that looks like on the inside.
From the outside, everyone in your company should be different in the same way.
On the inside, every individual should be sharply distinguished by her work.
The best thing I did as a manager at Paypal was to make every person in the company responsible for doing just one thing.

If you build it, will they come?
Customers will not come just because you build it. You have to make that happen, and it is harder than it looks.
If you have invented something new but you haven't invented an effective way to sell it, you have a bad business  - no matter how good the product.
Superior sales and distribution by itself can create a monopoly, even with no product differnentiation.
Your company needs to sell more than its product. You must also sell your company to employees and investors.
If Linkedin had tried to simply replace recruiters with technology, they wouldn't have a business today.

Man and Machine
Will a machine replace man?
Substitution vs complementarity?
Globalization means substitution
Technology means complementarity
As computers become more and more powerful, they won't be substitutes for humans, they will be complements.

Seeing Green
cleantech bubble
1. Can you create breakthrough technology instead of incremental improvements?
2. Is now the right time to start your particular business?
3. Are you starting with a big share of small market?
4. Do you have the right team?
5. Do you have a way to not just create but deliver your product?
6. Will your market position be defensible 10 and 20 years into the future?
7. Have you identified a unique opportunity that others don't see?

The Founder's Paradox
Founders are important not because they are the only ones whose work has value, but rather because a great founder can bring out the best work from everybody at his company.
Everything important to use - the universe, the planet, the country, your company, your life, and this very moment - is singular.
Our task today is to find singular ways to create new things that will make the future not just different, but better, to go from 0 to 1. The essential first step is to think for yourself. Only by seeing our world anew, as fresh and strange as it was to the ancients who saw it first, can we both recreate it and preserve for the future.