Tag:Full table

  • MySQL query optimization blade – explain


    There is one? There is a question: when an index field is used as a condition in a where query, will the index be used in the query? SELECT * FROM table_name WHERE column_index in (expr) Will the index be used in the above SQL query? With this problem, I found many articles on the […]

  • Record 30 methods of optimizing SQL query in MySQL


    1. To optimize the query, we should try to avoid full table scanning. First, we should consider building indexes on the columns involved in where and order by. 2. Avoid using in where clause as far as possible= Or < > operator, otherwise the engine will give up using the index and scan the whole […]

  • MySQL optimization 3 (optimization rules)


    When you look at the following content, make sure you can use explain to analyze SQL! Single index table optimization case: Question:For example, we want to query the first record sorted by user name with password = 666 and age > 30. After analysis, we will find that the SQL internal optimizer has carried out […]

  • [20210219] full table scan logic read problem.txt


    [20210219] full table scan logic read problem.txt –//General inquiry logic reading settings_ trace_ pin_ Time, or set 10200 events–//Settings_ trace_ pin_ One obvious disadvantage of time is that it is set at the system level and needs to be restarted, so that each process generates a lot of trace information–//Another factor is that for the […]

  • [20210220] full index scan fast index scan logical read.txt


    [20210220] full index scan fast index scan logical read.txt –//Yesterday, we tested the logical read problem of table scan. Today, we test the logical read problem of full index scan and fast index scan 1. Environment:[email protected]> @ver1PORT_STRING                    VERSION        BANNER—————————— ————– ——————————————————————————–x86_64/Linux 2.4.xx       Oracle Database 11g Enterprise Edition Release – 64bit Production [email protected]> alter […]

  • Several common writing methods leading to MySQL index invalidation


    Recently, I have been busy dealing with some SQL optimization problems left over from old projects. Due to the problems in the original table design and field design, with the growth of business, there are a lot of slow SQL, which leads to the soaring CPU resources of MySQL. Based on this, I’d like to […]

  • MySQL learning notes (9): index


    This article is updated on July 27, 2019, using MySQL 5.7 and operating system deepin 15.4. catalog Typical scenarios where indexes can be used A typical scenario in which an index exists but cannot be used View index usage When creating an n-column composite index, n indexes are actually created. The leftmost column set in […]

  • Analyze date with MySQL execution plan_ The influence of format function on index


    preface Recently, the company is using date in code review_ There are different views on format function. Specific date_ Will format affect the index? In which case will it have an impact? Nothing happened at the weekend, pass the MySQL implementation plan test wave. Using explain analysis The execution plan is to show how MySQL […]

  • A case of using union index


    In practice, full table scanning is always avoided, so slow table scanning is avoidedsqlIndex on some fields in. The significance of index building is that the original operation of scanning and operation in the table is executed in the index to reduce the number of scanning rows in the table, so the query speed is […]

  • Oracle indexing and SQL optimization


    Database index: The index has a single column indexOn compound index If a field of a table has a primary key constraint and a unique constraint, Oracle will automatically propose a unique index on the corresponding constraint column. Database index is mainly used to improve access speed. Construction principle: 1. Indexes should always be built […]

  • Index failure


    Index is a very important chapter for MySQL. There are also many knowledge points in the index. If you want to master them thoroughly, you need to break them one by one. Today, let’s talk about the situations that will lead to index failure. Picture summary Full value matching (best index) explain select * from […]

  • A slow query optimization of MySQL


    Recently, I met a slow query problem in mysql, so I checked it. Here is a summary of the related process. Positioning reasons First of all, I checked the slow query log of MySQL and found that such a query takes a long time (about more than one second) and scans a large number of […]