Shulou(Shulou.com)06/03 Report--

This article mainly explains "which SQL statements of mysql do not go to the index", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "mysql which SQL statements do not index" it!

It's not unfamiliar to mention indexes, but there are cases where indexes are not used in mysql, so let's take a look at the sql statements that don't take an index.

1. Index columns participate in expression evaluation.

SELECT 'sname' FROM' stu' WHERE 'age' + 10 = 30

2. Function operation.

SELECT 'sname' FROM' stu' WHERE LEFT ('date',4) < 1990

3.% words%-fuzzy query.

SELECT * FROM 'manong' WHERE `uname` LIKE' programmer%'- indexing SELECT * FROM 'manong' WHERE `uname` LIKE'% programmer%'- not indexing

4. Compare strings with numbers.

CREATE TABLE 'a' ('a'char (10)); EXPLAIN SELECT * FROM 'a'WHERE 'aindexes = "1"-- EXPLAIN SELECT * FROM 'a'WHERE 'aindexes 1-- not indexing, but also using functional operations

5. Or is included in the query condition.

Even if there is a conditional index, it will not be used. In other words, all fields required to be used must be indexed:

Select * from dept where dname='xxx' or loc='xx' or deptno = 45

6. Regular expressions do not use indexes.

The optimizer estimates that using a full table scan is faster than using an index and does not use an index.

The MySQL internal optimizer optimizes the SQL statement.

At this point, I believe that everyone on the "mysql which SQL statements do not go index" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.