Sahasrar's questions

请帮我为 MySQL 编写这个查询。我想从同一日期时间中选择最高值。因此，如果有多个相等的日期时间值，则只返回价格最高的一行。总而言之，每个时间戳一行，价格最高。

+------+---------------+--------------+---------+---------+---------------------+
|  id  | source_curr   | dest_curr    |  price  | act     |   timestamp         |
+------+---------------+--------------+---------+---------+---------------------+
|  164 | EUR           | USD          | 0.99332 | bids    | 2022-07-18 20:52:38 |
|  769 | EUR           | USD          | 0.99125 | bids    | 2022-07-18 20:52:38 |
| 1370 | EUR           | USD          | 0.99332 | bids    | 2022-07-18 20:52:38 |
|  568 | EUR           | USD          | 0.99322 | bids    | 2022-07-18 20:52:38 |
| 1572 | EUR           | USD          | 0.96335 | bids    | 2022-07-18 20:52:38 |
| 1771 | EUR           | USD          | 0.99432 | bids    | 2022-07-18 20:52:38 |
|  971 | EUR           | USD          | 0.99535 | bids    | 2022-07-18 20:52:38 |
|  367 | EUR           | USD          | 0.99334 | bids    | 2022-07-18 20:52:38 |
| 1173 | EUR           | USD          | 0.99332 | bids    | 2022-07-18 20:52:38 |
|  574 | EUR           | USD          |  0.9865 | bids    | 2022-08-11 15:33:22 |
| 1179 | EUR           | USD          |  0.9845 | bids    | 2022-08-11 15:33:22 |
|  373 | EUR           | USD          |  0.9863 | bids    | 2022-08-11 15:33:22 |
| 1578 | EUR           | USD          |  0.9875 | bids    | 2022-08-11 15:33:22 |
|  977 | EUR           | USD          |  0.9868 | bids    | 2022-08-11 15:33:22 |
| 1376 | EUR           | USD          |  0.9864 | bids    | 2022-08-11 15:33:22 |
| 1777 | EUR           | USD          |  0.9365 | bids    | 2022-08-11 15:33:22 |
|  170 | EUR           | USD          |  0.9165 | bids    | 2022-08-11 15:33:22 |
|  775 | EUR           | USD          |  0.9865 | bids    | 2022-08-11 15:33:22 |
|  773 | EUR           | USD          |    0.99 | bids    | 2022-08-11 17:50:31 |
| 1775 | EUR           | USD          |    0.99 | bids    | 2022-08-11 17:50:31 |
| 1576 | EUR           | USD          |    0.98 | bids    | 2022-08-11 17:50:31 |
|  572 | EUR           | USD          |    0.99 | bids    | 2022-08-11 17:50:31 |
|  168 | EUR           | USD          |    0.99 | bids    | 2022-08-11 17:50:31 |
|  371 | EUR           | USD          |    0.95 | bids    | 2022-08-11 17:50:31 |
| 1177 | EUR           | USD          |    0.99 | bids    | 2022-08-11 17:50:31 |
| 1374 | EUR           | USD          |    0.91 | bids    | 2022-08-11 17:50:31 |
|  975 | EUR           | USD          |    0.96 | bids    | 2022-08-11 17:50:31 |
|  581 | EUR           | USD          | 0.98465 | bids    | 2022-08-11 21:10:27 |
| 1784 | EUR           | USD          | 0.98564 | bids    | 2022-08-11 21:10:27 |
|  984 | EUR           | USD          | 0.98123 | bids    | 2022-08-11 21:10:27 |
|  782 | EUR           | USD          | 0.98132 | bids    | 2022-08-11 21:10:27 |
| 1383 | EUR           | USD          | 0.98115 | bids    | 2022-08-11 21:10:27 |
| 1585 | EUR           | USD          | 0.98848 | bids    | 2022-08-11 21:10:27 |
| 1186 | EUR           | USD          | 0.98185 | bids    | 2022-08-11 21:10:27 |
|  380 | EUR           | USD          | 0.98184 | bids    | 2022-08-11 21:10:27 |
|  177 | EUR           | USD          | 0.98148 | bids    | 2022-08-11 21:10:27 |
+------+---------------+--------------+---------+---------+---------------------+

我不知道为什么在我的“城市”表中搜索如此缓慢。我的查询正在寻找距离城市约 25 公里的“城市”表。我使用这个简单的查询，数据库需要将近 20 秒才能返回结果。

SELECT city_destination,distance FROM cities
     WHERE city_start='Wien'
       AND distance <= 25
     ORDER BY distance ASC

表引擎是 InnoDB。该表有约。700万行：

+--------------------+-------------+------+-----+---------+----------------+
| Field              | Type        | Null | Key | Default | Extra          |
+--------------------+-------------+------+-----+---------+----------------+
| id                 | int(11)     | NO   | PRI | NULL    | auto_increment |
| id_of_start        | int(11)     | NO   |     | NULL    |                |
| id_of_destination  | int(11)     | NO   |     | NULL    |                |
| city_start         | text        | NO   |     | NULL    |                |
| city_destination   | text        | NO   |     | NULL    |                |
| distance           | double      | NO   |     | NULL    |                |
+--------------------+-------------+------+-----+---------+----------------+

谁能告诉我如何优化数据库或查询？

我们有现有的表：

| id |    name    |   color   | calories |
------------------------------------------
| 1  | apple      | red       | 20       |
| 2  | orange     | orange    | 10       |
| 3  | grapes     | green     | 5        |
| 4  | bananas    | yellow    | 15       |
| 5  | plum       | purple    | 25       |
------------------------------------------

列id 是唯一的和主键。我在编辑/更新记录时遇到问题，例如，我注意更改第 3 行 where、id = 3toid = 5和第 5 行 where、id = 5to id = 3... 就像第 3 行和第 5 行之间交换 id。当然有重复，但是如何解决这种情况...

但是 MySQL 在逻辑上抛出了错误。

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5' for key 'PRIMARY'

我知道这是愚蠢的情况，但我们有一个客户想要那样做。他想编辑/更新每个现有记录。