DELETE后MySQL查询速度慢

时间:2021-12-22 00:53:57

I have a two part PHP script.

我有两部分PHP脚本。

  1. The first that deletes some rows in the database
  2. 第一个删除数据库中的某些行

  3. The second that triggers SELECT queries
  4. 第二个触发SELECT查询

The second script alone runs in about 0.2 sec. When both parts are processed, the second part takes 5 sec. The next time the second script runs alone, it's back to 0.2 sec.

第二个脚本单独运行大约0.2秒。处理两个部件时,第二部分需要5秒。下一次第二个脚本单独运行时,它会回到0.2秒。

Any clue?

2 个解决方案

#1


1  

Its likely that the query has cached so it runs faster the second time, more info here. If you add SQL_NO_CACHE to the query then you should be able to determine if the cache is a factor.

可能是查询已缓存,因此它第二次运行得更快,这里有更多信息。如果将SQL_NO_CACHE添加到查询中,那么您应该能够确定缓存是否是一个因素。

#2


0  

Your Query-Cache for a table is 'invalidated' each time the table is updated.

每次更新表时,表的查询缓存都会“无效”。

The result is that any UPDATE/DELETE/INSERT to your table, will clear the query-cache of that table, and force a fresh disk read for the next SELECT. The result will be a slower query.

结果是对表的任何UPDATE / DELETE / INSERT将清除该表的查询缓存,并强制为下一个SELECT读取新磁盘。结果将是一个较慢的查询。

Here's a link to the MySQL DOCs.

这是MySQL DOC的链接。

If your table is incredibly large, you may want to investigate MyISAM tables and a separate Key-Cache for yet better read performance. MyISAM = fast reads, fast writes, horrible concurrency for writes/udpates. InnoDB = mediocre speed for reads, good concurrency for writes/updates.

如果您的表格非常大,您可能需要调查MyISAM表和单独的Key-Cache以获得更好的读取性能。 MyISAM =快速读取,快速写入,写入/ udpates的可怕并发性。 InnoDB =读取的平庸速度,写入/更新的良好并发性。

-- J Jorgenson --

- J Jorgenson -

#1


1  

Its likely that the query has cached so it runs faster the second time, more info here. If you add SQL_NO_CACHE to the query then you should be able to determine if the cache is a factor.

可能是查询已缓存,因此它第二次运行得更快,这里有更多信息。如果将SQL_NO_CACHE添加到查询中,那么您应该能够确定缓存是否是一个因素。

#2


0  

Your Query-Cache for a table is 'invalidated' each time the table is updated.

每次更新表时,表的查询缓存都会“无效”。

The result is that any UPDATE/DELETE/INSERT to your table, will clear the query-cache of that table, and force a fresh disk read for the next SELECT. The result will be a slower query.

结果是对表的任何UPDATE / DELETE / INSERT将清除该表的查询缓存,并强制为下一个SELECT读取新磁盘。结果将是一个较慢的查询。

Here's a link to the MySQL DOCs.

这是MySQL DOC的链接。

If your table is incredibly large, you may want to investigate MyISAM tables and a separate Key-Cache for yet better read performance. MyISAM = fast reads, fast writes, horrible concurrency for writes/udpates. InnoDB = mediocre speed for reads, good concurrency for writes/updates.

如果您的表格非常大,您可能需要调查MyISAM表和单独的Key-Cache以获得更好的读取性能。 MyISAM =快速读取,快速写入,写入/ udpates的可怕并发性。 InnoDB =读取的平庸速度,写入/更新的良好并发性。

-- J Jorgenson --

- J Jorgenson -