• 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