There are 2 tables:

CREATE TABLE IF NOT EXISTS `shop_item_division` ( `item_id` int(10) unsigned NOT NULL, `division_id` int(10) unsigned NOT NULL, UNIQUE KEY `item_id` (`item_id`), KEY `division_id` (`division_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `shop_item_division` ADD CONSTRAINT `shop_item_division_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `shop_items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; CREATE TABLE IF NOT EXISTS `shop_items_order` ( `item_id` int(10) unsigned NOT NULL, `division_id` int(10) unsigned NOT NULL, `sort` int(10) unsigned NOT NULL, UNIQUE KEY `item_id` (`item_id`,`division_id`), KEY `division_id` (`division_id`), KEY `sort` (`sort`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `shop_items_order` ADD CONSTRAINT `shop_items_order_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `shop_item_division` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `shop_items_order_ibfk_2` FOREIGN KEY (`division_id`) REFERENCES `shop_item_division` (`division_id`) ON DELETE CASCADE ON UPDATE CASCADE; 

 SELECT shop_item_division.item_id FROM shop_items_order RIGTH JOIN shop_item_division ON shop_item_division.division_id = shop_items_order.division_id AND shop_item_division.item_id = shop_items_order.item_id WHERE shop_item_division.division_id = 103 ORDER BY shop_items_order.sort

Gives out:

 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: shop_items_order type: ref possible_keys: item_id,division_id key: division_id key_len: 4 ref: const rows: 2 Extra: Using where; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: shop_item_division type: eq_ref possible_keys: item_id,division_id key: item_id key_len: 4 ref: shop_items_order.item_id rows: 1 Extra: Using where 2 rows in set (0.00 sec)

How to implement item_id sampling and sorting by sort from the second table using the index, BUT! in the second table there may be no results, then sort out as horrible. THOSE. There are no lines in shop_items_order for all division_id

  • but if there is no record in shop_items_order, then where do you get the sorting order, in what order should the records be missing there. And something tells me that using the index for sorting does not work out - Mike
  • How then to implement? There is a table of correspondence between the section and the product, according to the section id we get the list of goods Further, sorting is not installed for all sections - in some it is not important, how to make sorting for all having entries? - lecherg
  • So now you are sorted and all who have records - Mike
  • An index must be used! - lecherg
  • But this is impossible ! First, the MySQL optimizer rarely uses indexes for sorting. Secondly, the optimizer in one database cannot use one index for join and another for sorting (and the connection of tables by a key in which the order field is not included). In the third order by is applied (any DBMS) to the entire sample as a whole, and in the whole sample there are records that are not found in any index - Mike

0