Mysql: Query which orders by default and rank (pin/hold up entries?)
You can add to the ORDER BY a IF ot CASE, depending on the complexity or the new order.
In you case you ned to add the max(id) into the mix


CREATE TABLE table1 (
  `id` INTEGER,
  `title` VARCHAR(18),
  `rank_index` VARCHAR(4)
);

INSERT INTO table1
  (`id`, `title`, `rank_index`)
VALUES
  ('1', 'An awesome product', NULL),
  ('2', 'Another product', '3'),
  ('3', 'Baby car', NULL),
  ('4', 'Green carpet', '1'),
  ('5', 'Toy', NULL);




    SELECT t1.id, t1.`title`,t1.`rank_index`
    FROM table1  t1 CROSS JOIN ( SELECT MAX(id)  maxid FROM table1) t2 
    ORDER BY IF(rank_index IS NULL, maxid - id + 1 , rank_index - 0.5) ASC


id | title              | rank_index
-: | :----------------- | :---------
 4 | Green carpet       | 1         
 5 | Toy                | null      
 2 | Another product    | 3         
 3 | Baby car           | null      
 1 | An awesome product | null      


db&lt;&gt;fiddle here