the interviewer said the index was perfect. I didn"t answer at that time.
the interviewer said the index was perfect. I didn"t answer at that time.
1, first of all, explain looks at the execution order of mysql and whether there is a "perfect" index used correctly;
2, then when it comes to 100w and 500w, see if the result is a small table driving a large table, if not, use a STRAIGHT_JOIN association to force the driver table to try;
3, see whether deferred queries can be used to override the index
.
personal opinion is not quite sure of the interviewer's intention, but the explain analysis must be
you can take a look at the following optimization ideas.
mysql two tables association optimization
now I have an array, which contains about 100000 of the data, all personId,. I now need to use this ID to query other data in another table. Is there any good way to make the query more efficient? Whether IN can be used or not, although it is easy to wri...
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? ...
I have a table exchange CREATE TABLE `exchange` ( `id` int(11) NOT NULL AUTO_INCREMENT, `rank` int(11) NOT NULL, `name` varchar(255) NOT NULL, `volume` varchar(255) NOT NULL, `timestamp` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB...
< H1 > question < H1 > the data table needs a column to keep the current time, and then it needs to be accurate to milliseconds , and then search it. It is found that datetime can take decimals. According to this blog TIMESTAMP(3) DATETIME(3)3 T...
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...
doubts about subqueries and table join queries there are two tables, user (user) and user_dictionary (user Dictionary), and then my colleague struggles with me with a problem. Forgive me for this scum who doesn t know . related codes user: industr...
problem description recently, I have been optimizing the related business of the system learn that you can improve the performance of db through thread pooling here comes the problem mysql has its own thread pool. You can add parameters such as ...
for example, ten thousand people vote for one person what I do is to read the number of votes first and then get the votes + 1 , but if ten thousand people vote at the same time, the number of votes and updates must be incorrect . The code is as foll...
reason suppose I want to input all the data of a number table, how can I change the value of this field to B if there is A? INSERT INTO `wp_posts` (`ID`, `content`) VALUES (13335, <h3>< h3> r n[embed]https: www.instagram.com p Bc...
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...
CREATE DEFINER=`root`@`%` FUNCTION `is_mixed`(str1 TEXT, str2 TEXT) RETURNS tinyint(4) BEGIN DECLARE ismixed TINYINT DEFAULT 0; set ismixed:=(select concat(str1, , ) regexp concat(replace(str2, , , ,| ), , )); RETURN ismixed; EN...
problem description specific error message is General error: 1205 Lock wait timeout exceeded; try restarting transaction the reason for this problem is that there is a process that has been occupying the lock and does not release it. How do you repr...
use directly alter table order_log add outdated int default 0 null; it hasn t been over for a long time is there any way to speed it up? ...
for example, select * from table limit 100 if there are 1 million data in the table, , then this statement will scan the whole table, filter out all the 1 million data that meets the requirements , and then fetch the first 1 million rows . and if you ...
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 ...
Boss, the following sentence SQL, the execution time has reached 6s, what should I do? in the end, I chose the scheme of "create a separate index " with the least impact. not sure this is the best result! just because of my environment, comprehens...