1, Cartesian product
The principle of large memory consumption is to multiply two tables first , Data filtered from the multiplication results .
2, Lianjie query
Memory consumption is lower than multi table query , Every piece of data needs to be judged in the query .
Let's talk about it , For example, Cartesian product and connection query two people go to buy a video card , Yeah, they're both in the video card business . Cartesian product is to buy a batch of products and store them in its own warehouse . And then check whether they are qualified one by one , Then resell . The connection query is to check whether it is good to buy a video card first , If it's good, he'll take delivery and resell it, or he won't take delivery , He doesn't have his own warehouse , Of course, people don't need warehouses .
In theory, there won't be much difference between the two , Because two people need to match the video card one by one . It's just that connection query doesn't need that warehouse . In fact, when the data volume is very large, neither is the best solution . Because you can imagine , If you enter 100 10000 video cards . Every need 100 Only program can test whether it is qualified or not . That's up to 100 million . therefore
When using multi table query , If the amount of data is not large , The efficiency of connection query is relatively high . But if the amount of data is huge , Then use subquery （ If not available , Even divided into several single table queries ） Query efficiency will be higher .
Query statement where If there are multiple conditions after the condition , The order of conditions should be ?
Put the conditions that can filter a large amount of data first , Other conditions behind
Then let's talk about it Limit
It has two parameters ,srtart and length Which data do you want to start with , How many pieces of data to query .
Pagination query is the most common usage scenario
Select * from surface limit 0,10
Select * from surface limit (( Page -1)*10),10
however mysql Here's a question, for example, from the 9000 Ten thousand pieces of data look for ten pieces of data , that mysql You have to count to number one 9000 Tens of thousands more .
To solve this problem
You can add a field num It's continuous , The biggest disadvantage is the requirement num It's continuous . It's like learning to assemble , Efficiency of linked lists and arrays , Array can directly find the specified number of data , And the list must start from the first , Count to the specified data .
Select * from surface where num >=(( Page -1)*10) limit 10;
（oracle Sub query is used in database paging query , Not used limit）
It's better not to use it when writing queries *
Instead, write down the fields to be queried