(turn) MySQL actual combat or, in and union ALL query efficiency

The OR, in, and union all queries are efficient in which fast.

Many of the sounds on the web are that union all is faster than or, in, because or, in causes full table scan, they give a lot of examples.

But is the union all really faster than or, in? This paper is to use practical examples to explore the efficiency between them.

1: Create the table, insert the data, the data volume is 10 million "no effect is not obvious".

SQL code

1. Drop table if EXISTS BT;
2. Create table BT (
3. ID Int (Ten) is not NUll,
4. VName varchar ( DEFAULT ' not NULL ' ) ,
5. PRIMARY Key (ID)
6. ) Engine=innodb;

The table has only two field IDs like the primary Key "index page", and the other is a normal field. (Lazy to use simple table structure it)

Inserting 10 million data into the BT table

Here I wrote a simple stored procedure "so your MySQL version is at least greater than 5.0, my version is 5.1", the code is as follows.

Note: It's best

INSERT into BT (id,vname) VALUES (i, CONCAT (' M ', i));---1

Revision changed to

INSERT into BT (id,vname) VALUES (i, CONCAT (' M ', I, ' TT '));---2

reason for modification in

Non-indexed columns and vname use a joint for a full scan, use 1 .

Non-indexed columns and vname use a full-table scan, use 2 .

SQL code

2. DROP PROCEDURE IF EXISTS test_proc;
4. CREATE PROCEDURE test_proc ()
6. BEGIN
8. DECLARE i int default 0;
10. Set autocommit = 0;
12. While i<10000000 do
14. INSERT into BT (id,vname) VALUES (i, CONCAT ( ' M ', i));
16. Set i = i+1;
18. If i%2000 = 0 Then
20. Commit
22. End If;
24. End while;
26. END;

Don't write notes, it's quite simple.

The stored procedure is best set under Innob related parameters "primary and log, write cache related so that can speed up the insertion", I did not set insert 10 million data inserted for 6 minutes.

Some of the data are as follows: 10 million data similar

2: Combat

2.1: Use or, in, union All on index columns, respectively

The table we created has only the primary key index, so we can only query it with ID. We look for three data with ID 98,85220,9888589 for each of the following time:

Time is 0.00, how can this, hehe all queries are in the millisecond level.

I use other tools--ems SQL Manager for MySQL

Query display time is

94ms,93 MS, the time difference is almost negligible.

And then we're looking at our respective execution plans.

Here to note the field type with ref field

We found that the type "type for the union ALL" was used to show what kind of connection to use is ref and OR and in as range "ref connection type is better than range, not much difference", and the query row count is the same "See Rows field is 3".

From the whole process, it is not much different to use constant or in and union all queries in an indexed column.

But why in some complex queries, the re-indexed column uses OR and in is much slower than the union all speed, which may be that your query is not well written enough to allow MySQL to discard the index and perform a full table scan.

2.2: The OR, in, and union all are used in non-indexed columns.

We check the VName for the m98,m85220,m9888589 three data each time the following:

We find out why the union all query time is almost three times times that of OR and in.

This is why, we do not say first, first look at the three query plan.

Here we find the plan almost the same.

But we have to pay attention to the scan. For OR and in, the table is scanned only once, and rows is listed as 9664782.

For union all, the table was scanned three times and is 9664782*3.

That's why we see union all as almost three times times the reason.

Note: If you use a stored procedure that uses the second SQL for all of the type columns listed as all, this is the most I would like to demonstrate, but now it is almost finished to find the problem will wrong.

3: summary

3.1: Do not be superstitious union all is faster than or and in, to combine the actual situation to analyze exactly which case to use.

3.2: For indexed columns It is best to use union all, because complex queries such as "include operations" will cause the or, in to discard the index and the full table, unless you can determine whether or, in will use the index.

3.3: For only non-indexed fields say you are honest with or or in, because the non-indexed field would have been full table scan and union all only multiplied the number of table scans.

3.4: When the indexed field "valid" and contains non-indexed fields, you can also use or, in, or the union all,

But I recommend using or, in.

such as the following query:

SQL code

1. SELECT * from BT where BT. VName = ' M98 ' or bt.id =' 9888589 '
3. SELECT * from BT where BT. VName = ' M98 '
4. UNION All
5. SELECT * from bt where bt.id = ' 9888589 '

The two query speed difference depends mainly on the index column query length, such as index column query time is too long, then you also use or or in instead of it.

3.5: The above is mainly for a single table, and multi-table joint query, the place to consider more, such as connection, query table data volume distribution, index, etc., combined with a single-table strategy to choose the right keywords.

Personal views are for reference only and need to choose the right keywords for the actual data use case test .... .......

Above Test mysql5.1

Original sticker: http://xianglp.iteye.com/blog/869892

(turn) MySQL actual combat or, in and union ALL query efficiency