when there are too many SQL pages (for example, limit 1000000,20), the efficiency will be significantly reduced. How to optimize it? Here, take mysql as an example
when there are too many SQL pages (for example, limit 1000000,20), the efficiency will be significantly reduced. How to optimize it? Here, take mysql as an example
SELECT * FROM product WHERE ID > = (select id from product limit 1000000,1) limit 20;
SELECT * FROM product WHERE ID > = (select id from product limit 1000000,1) limit 20;
I have a solution here. I learned this before by reading the materials. I forgot exactly where I did. if you have a better idea, you can exchange it.
create an index table , which provides sequential relationships related to keys in the target table, you can join this index table to the target table, and use the where clause to get the desired rows more efficiently.
CREATE TABLE test (
test_no int not null auto_increment,
id int not null,
primary key(test_no),
unique(id)
);
TRUNCATE test;
INSERT INTO test (id) SELECT id FROM mytable ORDER BY id;
SELECT mytable.*
FROM mytable
INNER JOIN seq USING(id)
WHERE test.test_no BETWEEN 1000000 AND 1000020;such as the title. see that MySQL generates a tree for the primary key, and the leaf node holds the row data corresponding to the primary key. The secondary index leaf node holds the value of the primary key. excuse me: does the leaf node of th...
has such a logic, a piece of data, if there is in the database, then update, if it does not exist, then insert. But now there are ten thousand. So how to accomplish the task efficiently? ...
when querying, you need to query the scope according to the time field, timestamp type. Statement does not have subqueries, associated queries, only look up this table. it takes about 4.5 seconds to find 50, 000 results. This field has been indexed, bu...
there are two tables Table 1: Student number 1 | Student number 2 | Student number 3 001 | 002 | 003 Table 2: Student number | name 001 | Zhang San 002 | Li Si 003 | Wang Wu desired results: student number 1 | name 1 | student number 2 | nam...
problem description there is a user table with in it. uid regdate reg_package reg_channel ... now I have a SQL query for user data SELECT * FROM `user` ORDER BY `regdate` DESC LIMIT 0,50; there is a multi-column index on this table. i...
I have seen a lot of articles on mysql indexes that say that if a field has a small range of values (a large number of duplicate values), there is no need to establish an index. but my actual test: user_type_id field has an index: then executi...
the database currently has 1 million data (it has been running for about half a month) the following sentence takes 6 seconds to run (1 million results for where alone), and both user_id and time are indexed ...
Test database downloaded on github. Index: explain select: query the scope of the date field of the employees table if you want to query the recruits in a certain period of time, 1. Why interpret the results, using the type field or ALL? What doe...