SQL statement optimization principle and million data optimization scheme

1, use the index to traverse the table more quickly.
The index established by default is not a clustered index, but sometimes it is not optimal. In non-clustered indexes
, the data is physically stored randomly on the data page. A reasonable index design should be based on
On the analysis and prediction of various inquiries. Generally speaking:
A. Columns that have a large number of duplicate values and often have scope queries (>,<,> =,< =) and order by and group by, can be tested
Consider establishing a clustered index;
B. Multiple columns are frequently accessed at the same time, and each column contains duplicate values to consider establishing a composite index;
C. Composite indexes to maximize the indexing of critical queries, the leading columns must be the most frequently used columns. Indexes can help improve performance but not the more indexes the better, just the opposite. Too many indexes cause the system to be inefficient. When the user adds an index to the table, maintaining the collection of indexes will update the work accordingly.
2, in the mass query as little as possible with the format conversion.
3. The order BY and Gropu by using a by and group by phrase, any index contributes to the performance improvement of the Select.
4, any operation of the column will result in table scan, which includes the database tutorial functions, evaluation expressions, and so on, when the query to move the action to the right side of the equal sign as much as possible.
5, IN, or clauses often use a worksheet to invalidate the index. If you do not produce a large number of duplicate values, you can consider the sentence to be opened. The open clause should contain an index.

MySQL's Optimization principle 2:
1, as long as you can meet your needs, should use a smaller data type as far as possible: for example, using mediumint instead of int
2, try to set all columns to NOT NULL, if you want to save NULL, manually set it, instead of setting it as the default value.
3, as little as possible with varchar, TEXT, BLOB type
4, if your data is only a few of the few you know. It is best to use the enum type
5, as Graymice said, to build an index.

Method Two

Prior to optimization: A table data creates redundancy
SELECT ' t '. ' img_id ', ' t '. ' Thumb_path '
From ' Gallery_photofiles ' P
Left JOIN ' gallery_thumbs ' t on ' t '. ' img_id ' = ' P '. ' img_id ' and t.thumb_type= ' 11 '
WHERE ' P '. ' owner_user_id ' = ' 1 '
and p.img_id in (select a.img_id from ' Gallery_album_img_link ' A WHERE a.img_id)
After optimization: COUNT (*) greatly improves speed
SELECT ' t '. ' img_id ', ' t '. ' Thumb_path '
From ' Gallery_photofiles ' P
Left JOIN ' gallery_thumbs ' t on ' t '. ' img_id ' = ' P '. ' img_id ' and t.thumb_type= ' 11 '
WHERE ' P '. ' owner_user_id ' = ' 1 '
and (select COUNT (*) from ' Gallery_album_img_link ' A WHERE a.img_id=p.img_id) <1

Always thought that the MySQL tutorial random query several data, use

SELECT * from ' table ' ORDER by RAND () LIMIT 5
It's OK.
But the real test was found to be very inefficient. A library of more than 150,000, query 5 data, incredibly more than 8 seconds

View the official manual, also said that Rand () is executed multiple times in the ORDER BY clause, which is naturally inefficient and very low.

You are cannot use a column with RAND () of the clause, because order by would evaluate the column multiple time S.

Search Google, the Internet is basically query Max (ID) * RAND () to randomly obtain data.

SELECT *
From ' table ' as T1 JOIN (select ROUND (RAND () * (SELECT MAX (ID) from ' table ') as ID) as T2
WHERE t1.id >= t2.id
ORDER BY T1.id ASC LIMIT 5;
But this will produce a continuous 5 records. The solution can only be one query at a time, query 5 times. Even so, because of the 150,000 table, the query only needs 0.01 seconds.

The above statement uses a join,mysql forum where someone uses

SELECT *
From ' table '
WHERE ID >= (SELECT FLOOR (MAX (ID) * RAND ()) from ' table '
ORDER by ID LIMIT 1;
I tested it, it takes 0.5 seconds, and the speed is good, but there is still a big gap with the above statement. There is something wrong with the total sleep.

So I rewrote the statement.

SELECT * from ' table '
WHERE ID >= (SELECT Floor (RAND () * (select MAX (ID) from ' table '))
ORDER by ID LIMIT 1;
This, the efficiency is increased, the query time is only 0.01 seconds

Finally, the statement to improve, plus the min (id) judgment. I was at the beginning of the test, because I did not add min (id) judgment, the result is half of the time is always query to the first few lines in the table.
The full query statement is:

SELECT * from ' table '
WHERE ID >= (select floor (ID) from ' table ') + (select min (ID) () () + (()) + (() () + (() () () () + ("()") M ' table '))
ORDER by ID LIMIT 1; SELECT *
From ' table ' as T1 JOIN (select ROUND (select MAX (ID) from ' table ') (select min (IDs) from ' tables ')) + (select min () d) from ' table ') as ID) as T2
WHERE t1.id >= t2.id
ORDER by t1.id LIMIT 1;