Database table without index

Coffee_is_life

Hello forum,

in the last few weeks i tweaked our database and i got a few question about indexes.

I added the “log_slow_querys” key to mariadb (all version in my signature) to get some insights about long running querys.
most of them are using “join” commands for example:

delete syncedmessages.* FROM syncedmessages LEFT JOIN syncs ON syncs.id = syncedmessages.sync_id WHERE syncs.id IS NULL;

which was running over 15 seconds.

i made the same query with an select:

select syncedmessages.* FROM syncedmessages LEFT JOIN syncs ON syncs.id = syncedmessages.sync_id WHERE syncs.id IS NULL;

which was running 2.27 seconds (with is better but obviously no results due to the previous automatic delete command)

when i dig a bit deeper with “explain”:

explain extended select syncedmessages.* FROM syncedmessages LEFT JOIN syncs ON syncs.id = syncedmessages.sync_id WHERE syncs.id IS NULL;

i get:

+------+-------------+----------------+--------+---------------+---------+---------+-------------------------------+---------+----------+--------------------------------------+
| id   | select_type | table          | type   | possible_keys | key     | key_len | ref                           | rows    | filtered | Extra                                |
+------+-------------+----------------+--------+---------------+---------+---------+-------------------------------+---------+----------+--------------------------------------+
|    1 | SIMPLE      | syncedmessages | ALL    | NULL          | NULL    | NULL    | NULL                          | 8251697 |   100.00 |                                      |
|    1 | SIMPLE      | syncs          | eq_ref | PRIMARY       | PRIMARY | 4       | zarafa.syncedmessages.sync_id |       1 |   100.00 | Using where; Using index; Not exists |
+------+-------------+----------------+--------+---------------+---------+---------+-------------------------------+---------+----------+--------------------------------------+
2 rows in set, 1 warning (0.00 sec)

(since this exact command was executed some seconds ago this comes from cache, thats why its 0.00 sec)
but the fact that there is the “possible_keys = NULL” in the syncedmessages table i wanted to ask: why?
Is it normal that this table does not use indeces or is this a failure in our db setup?

best regards,
coffee_is_life