Neuesten Datensatz aus Datenbank holen

[saftmeister]

So, ich hab mir mal die Arbeit gemacht, das zu prüfen, und mein Gefühl hat mich nicht getäuscht:

mysql> DESCRIBE tests;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(50) | NO   |     | NULL    |                |
| phone   | varchar(50) | NO   |     | NULL    |                |
| email   | varchar(50) | NO   |     | NULL    |                |
| country | varchar(50) | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> SELECT *
    -> FROM tests
    -> ORDER BY id DESC
    -> LIMIT 1 ;
+--------+----------+--------------+--------------------+---------+
| id     | name     | phone        | email              | country |
+--------+----------+--------------+--------------------+---------+
| 284334 | Veda Guy | 889-977-7960 | Suki@ridiculus.gov | Albania |
+--------+----------+--------------+--------------------+---------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT *
    -> FROM `tests`
    -> ORDER BY `id` DESC
    -> LIMIT 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | tests | index | NULL          | PRIMARY | 4       | NULL |   1 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

Nun mit deiner Variante:

mysql> SELECT *
    -> FROM tests
    -> WHERE id
    -> IN (
    -> SELECT MAX( id )
    -> FROM tests
    -> );
+--------+----------+--------------+--------------------+---------+
| id     | name     | phone        | email              | country |
+--------+----------+--------------+--------------------+---------+
| 284334 | Veda Guy | 889-977-7960 | Suki@ridiculus.gov | Albania |
+--------+----------+--------------+--------------------+---------+
1 row in set (0.35 sec)

mysql> EXPLAIN SELECT *
    -> FROM tests
    -> WHERE id
    -> IN (
    -> SELECT MAX( id )
    -> FROM tests
    -> );
+----+--------------------+-------+------+---------------+------+---------+------+--------+------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref | rows   | Extra                        |
+----+--------------------+-------+------+---------------+------+---------+------+--------+------------------------------+
|  1 | PRIMARY            | tests | ALL  | NULL          | NULL | NULL    | NULL | 283360 | Using where                  |
|  2 | DEPENDENT SUBQUERY | NULL  | NULL | NULL          | NULL | NULL    | NULL |   NULL | Select tables optimized away |
+----+--------------------+-------+------+---------------+------+---------+------+--------+------------------------------+
2 rows in set (0.01 sec)

Die Tabelle hat ein paar mehr Datensätze:

mysql> SELECT COUNT(id) FROM tests;
+-----------+
| COUNT(id) |
+-----------+
|    284334 |
+-----------+
1 row in set (0.19 sec)

Der Datenbanktyp ist InnoDB und der Index-Typ ist BTREE. Also Default-Einstellungen bei MySQL 5.1.

Will damit natürlich keinen Krieg vom Zaun brechen ;-)