find the way to prevent full table scan in drugs table

[kiang]

Many queries related to drugs table caused full table scan and the indexes not work as expected.

SELECT Drug.id, Drug.license_id, Drug.vendor_id, Drug.manufacturer_description, License.id, License.name, License.name_english, License.license_id, License.expired_date, License.image, Vendor.name, Vendor.country FROM olc_drugs.drugs AS Drug LEFT JOIN olc_drugs.licenses AS License ON (Drug.license_id = License.id) LEFT JOIN olc_drugs.vendors AS Vendor ON (Drug.vendor_id = Vendor.id) WHERE 1 = 1 GROUP BY Drug.license_id ORDER BY License.count_daily DESC, License.count_all DESC, License.submitted DESC, Drug.id ASC LIMIT 80, 20;

explained: +----+-------------+---------+--------+---------------+---------+---------+---------------------------+--------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+--------+---------------+---------+---------+---------------------------+--------+-------+ | 1 | SIMPLE | Drug | ALL | NULL | NULL | NULL | NULL | 159435 | | | 1 | SIMPLE | License | eq_ref | PRIMARY | PRIMARY | 36 | olc_drugs.Drug.license_id | 1 | | | 1 | SIMPLE | Vendor | eq_ref | PRIMARY | PRIMARY | 36 | olc_drugs.Drug.vendor_id | 1 | | +----+-------------+---------+--------+---------------+---------+---------+---------------------------+--------+-------+

show create table drugs: CREATE TABLE drugs ( id binary(36) NOT NULL COMMENT '主索引', license_id binary(36) NOT NULL COMMENT '藥證索引', vendor_id binary(36) NOT NULL COMMENT '申請商編號', manufacturer_description varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '製程', PRIMARY KEY (id), KEY vendor_id (vendor_id), KEY drugs_license_id (license_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci