Is there any way to get this order by query to start searching from the given timestamp on a MySQL i-CodePudding

I am working on a mysql 5.6 database, and I have a table looking something like this:

CREATE TABLE `items` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  `node_type_id` int(11) NOT NULL,
  `property_native_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `parent_item_id` bigint(20) DEFAULT NULL,
  `external_timestamp` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_items_on_acct_node_prop` (`account_id`,`node_type_id`,`property_native_id`),
  KEY `index_items_on_account_id_and_external_timestamp` (`account_id`,`external_timestamp`),
  KEY `index_items_on_account_id_and_created_at` (`account_id`,`created_at`),
  KEY `parent_item_external_timestamp_idx` (`parent_item_id`,`external_timestamp`),
) ENGINE=InnoDB AUTO_INCREMENT=194417315 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I am trying to optimize a query doing this:

SELECT * 
FROM items 
WHERE parent_item_id = ? 
AND external_timestamp < ( SELECT external_timestamp 
                           FROM items 
                           WHERE id = ? 
                          ) FROM items ORDER BY 
external_timestamp LIMIT 5

Currently, there is an index on parent_item_id, so when I run this query with EXPLAIN, I get an "extra" of "Using where; Using filesort"

When I modify the index to be (parent_item_id, external_timestamp), then the EXPLAIN's "extra" becomes "Using index condition"

The problem is that the EXPLAIN's "rows" field is still the same (which is usually a couple thousand rows, but it could be millions in some use-cases).

I know that I can do something like AND external_timestamp > (1 week ago) or something like that, but I'd really like the number of rows to be just the number of LIMIT, so 5 in that case.

Is it possible to instruct the database to lock onto a row and then get the 5 rows before it on that (parent_item_id, external_timestamp) index?

CodePudding user response：

(I'm unclear on what you are trying to do. Perhaps you should provide some sample input and output.) See if this works for you:

SELECT i.* 
    FROM items AS i
    WHERE i.parent_item_id = ? 
      AND i.external_timestamp < ( SELECT external_timestamp 
              FROM items 
              WHERE id = ? )
    ORDER BY i.external_timestamp
    LIMIT 5

Your existing INDEX(parent_item_id, external_timestamp) will probably be used; see EXPLAIN SELECT ....

If id was supposed to match in all 5 rows, then the subquery is not needed.

CodePudding user response：

SELECT items.* 
FROM items 
CROSS JOIN ( SELECT external_timestamp
             FROM items 
             WHERE id = ? ) subquery
WHERE items.parent_item_id = ? 
AND items.external_timestamp < subquery.external_timestamp
ORDER BY external_timestamp LIMIT 5

id is PK, hence the subquery will return only one row (or none).