Mariadb – Can mariaDB contain indices only with data coming from remote tables

Query 1:

INDEX(person_type, person_id) -- in that order
INDEX(person_type, person_id, full_name) -- to be "covering"

Query 2 ("covering" is not practical because of *):

INDEX(person_type, full_name) -- in that order

Query 3: Before making suggestions here, please explain why you have a LIMIT without an ORDER BY.

See also my cookbook on making indexes.

You can really benefit from an index on hosts (hostname, scan_id) for this query, and possibly another one including status (especially for the second query below). Your query may also benefit from transferring some joins to per-row totals:

CREATE INDEX idx_hostname_scanid ON hosts (hostname, scan_id);
CREATE INDEX idx_hostname_status_scanid ON hosts (hostname, status, scan_id);

SELECT hosts.hostname,
       INET_NTOA(hosts.ip_address) AS ip,
       CONV(hosts.mac_address, 10, 16) AS mac,
       hosts.mac_vendor AS mac_vendor,
       hosts.status AS status,
       scans.start_time AS last_scan,
       (SELECT MAX(scans.start_time)
        FROM hosts
        JOIN scans ON (scans.scan_id = hosts.scan_id)
        WHERE hosts.hostname = t.hostname AND hosts.status = 'up') AS last_seen
FROM (
       -- ID of most recent scan for each host
       SELECT MAX(hosts.scan_id) AS max_scan_id, hosts.hostname
       FROM hosts
       WHERE hosts.hostname IN ('foo', 'bar', 'baz')
       GROUP BY hosts.hostname
     ) t
JOIN hosts ON (hosts.hostname = t.hostname AND hosts.scan_id = t.max_scan_id)
JOIN scans ON (scans.scan_id = t.max_scan_id);

Also, considering that you already trust the last scan to be the one with the highest id, you may speed-up your query by trusting the last_seen time to be the one with the highest id:

CREATE INDEX idx_hostname_scanid ON hosts (hostname, scan_id);
CREATE INDEX idx_hostname_status_scanid ON hosts (hostname, status, scan_id);

SELECT hosts.hostname,
       INET_NTOA(hosts.ip_address) AS ip,
       CONV(hosts.mac_address, 10, 16) AS mac,
       hosts.mac_vendor AS mac_vendor,
       hosts.status AS status,
       scans.start_time AS last_scan,
       lss.start_time AS last_seen
FROM (
       -- ID of most recent scan for each host
       SELECT MAX(hosts.scan_id) AS max_scan_id, hosts.hostname
       FROM hosts
       WHERE hosts.hostname IN ('foo', 'bar', 'baz')
       GROUP BY hosts.hostname
     ) t
JOIN hosts ON (hosts.hostname = t.hostname AND hosts.scan_id = t.max_scan_id)
JOIN scans ON (scans.scan_id = t.max_scan_id)
LEFT JOIN (
       SELECT MAX(hosts.scan_id) AS max_scan_id, hosts.hostname
       FROM hosts
       WHERE hosts.hostname IN ('foo', 'bar', 'baz') AND hosts.status = 'up'
       GROUP BY hosts.hostname
     ) ls ON (ls.hostname = t.hostname)
LEFT JOIN scans lss ON (lss.scan_id = ls.max_scan_id);