Which of the two query logics should be used?

Method 1: {code ...} method 2: {code ...} I am a beginner, but I still want to ask a question. I thought I did not discuss this case. But recently I found that someone used the second method, internal system, with a low access volume, so there is no impact, but he said the first option will increase mysq... method 1:

sql = "select {xxx} from student A,class B where A.class_id=B.id and A.id in {xxxx}"res = excute(sql)

Method 2:

sql = "select * from student where id in {xxxx}"res = excute(sql)foreach(res as v){    sql2 = "select {xxx} from class where id = v[class_id]";    res2 = excute(sql2)    res[xxx] = res2[xxx]}

It's a beginner's question, but I 'd like to ask
I used method 1 in this case and thought I did not discuss it;
However, it was recently discovered that some people use the second method, the internal system, and the access volume is small, so there is no impact, but he said that the first method will increase the pressure on mysql and may cause mysql faults, I was hesitant.
Thank you for your analysis.

Reply content:

Method 1:

sql = "select {xxx} from student A,class B where A.class_id=B.id and A.id in {xxxx}"res = excute(sql)

Method 2:

sql = "select * from student where id in {xxxx}"res = excute(sql)foreach(res as v){    sql2 = "select {xxx} from class where id = v[class_id]";    res2 = excute(sql2)    res[xxx] = res2[xxx]}

It's a beginner's question, but I 'd like to ask
I used method 1 in this case and thought I did not discuss it;
However, it was recently discovered that some people use the second method, the internal system, and the access volume is small, so there is no impact, but he said that the first method will increase the pressure on mysql and may cause mysql faults, I was hesitant.
Thank you for your analysis.

The principle of this problem is that if you can simply use SQL to calculate, it will be calculated in the database and then the results will be cached.

So personal preference method 1.

If you can use SQL computing, you can perform operations in the database. = is your own php statements more efficient than SQL statements? Do not think that mysql is too fragile.
Simplify the code as much as possible .. First.

Although Join queries are the first type, they only access the database once, and the index is not slow.

The second type will certainly not be used ~

The first type is more concise, and the first type is generally used.
What kind of performance is better?studentAndclassThe two tables are related to their relative sizes.
If the system load in the second method is low, you can optimize the SQL statement as follows:

sql = "select {xxx} from (select * from student where id in (1,2,3))A,class B where A.class_id=B.id "

If the data in the class table can be retrieved from the database every time and all the data is cached, The second idea is acceptable. After all, there won't be too many classes

There is a connection operation in the first type. It is very difficult for the database to perform the connection operation (especially when many table records exist), and the query operation is very fast.

First, your second SQL statement is obviously incorrect. join in the program, at least we should write this:

Pseudo code:

sql = "select * from student where id in {xxxx}"res = excute(sql)string idsforeach(res as v){    // append v[class_id] to ids.}// ids is now like "1, 3, 4"sql2 = "select {xxx} from class where id in ( $ids )";res2 = excute(sql2)

This requires only two queries, and your implementation is obviously in the second method.

Join is still performed in the program in SQL, and is discussed in High Performance Mysql. The third and sixth sections include "Complex Queries Versus into Queries" and "Join Decomposition.

Splitting multi-table join into multiple single-table queries has the following advantages ):

• Caching can be more efficient.
• Executing the queries inpidually can sometimes reduce lock contention.
• Doing joins in the application makes it easier to scale the database by placing tableson different servers.
• The queries themselves can be more efficient.
• You can reduce redundant row accesses.
• To some extent, you can view this technique as manually implementing a hashjoin instead of the nested loops algorithm MySQL uses to execute a join. A hash join might be more efficient.> undefined> undefined

Yes. Apparently, the first type is used.

Why?

What are the main uses of SQL?
What is the calculation order of magnitude of SQL script?
Excute does not seem to be supported in the T-SQL92 specification, right?

The upstairs is correct. The landlord is definitely a senior hacker.

I want to say, how do you want to mix computing, then at least the content related to the foreach return value in the middle, do you want to do it with this program?

Summary

If you do not select method 1 here, you should say nothing to me.

It's not a preference, It's a heap of code.PrinciplesProblem: 1. Reduce the number of SQL lines as much as possible. 2. Optimize the SQL Execution efficiency.
Method 2: Write the SQL statement in the circular code to write the code.DajiNo matter how lightweight your work is.

PS: It is basically impossible to obtain a result set in the SQL text. Therefore, you should not issue the SQL text multiple times to solve the query result set patchwork problem.

The second type will certainly not be used, because mysql is slower than the current language, and the query is easier to optimize on the statement side, and SQL is easier to maintain.