I made two tables, A and B, each have 4 w article data, (two table, one to one correspondence)
A table main fields: id (primary key), show_date (time field, string, indexing)
Table B main fields: id (primary key), a_id (associated with A table id), show_date (time fields, string, indexing, and A table data is the same),

The first experiment:

 
SELECT COUNT (Anderson, d) 
FROM A, B 
WHERE 
A.. 
 show_date LIKE '2020%'AND B.a _id=Anderson, d; 

1000 ms query time,


The second experiment:

 
SELECT COUNT (Anderson, d) 
FROM A, B 
WHERE 
 .. 
 show_date LIKE '2020%'AND B.a _id=Anderson, d; 

200 ms query time,


A third experiment:
Remove the id primary key constraints A mark

 
SELECT COUNT (Anderson, d) 
FROM A, B 
WHERE 
A.. 
 show_date LIKE '2020%'AND B.a _id=Anderson, d; 

1000 ms query time,

Don't understand is what reason, have a great god can give me to reassure orz...

CodePudding user response:

The first query time write wrong, should is 10000 ms,
Two tables associated with the main key, query 10 times slower than without primary key link, don't know what the reason,

CodePudding user response:

Look at the execution plan and let us know about the value of the individual SQL

CodePudding user response:

Query for the third time you should direct reuse of the query results for the first time, because the SQL code is completely consistent, but estimates that no physical read, is not really can not control experiment shows that the problem

CodePudding user response:

This a few queries, no matter which table id field, whether the primary key for the entire query does not have any effect,
The COUNT in the field, and the LIKE '2020%' use of field, if the source from a table, efficiency is high efficiency from different watch,