gistfile1.md

Schema

CREATE TABLE `ext_log_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `action` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
  `logged_at` datetime NOT NULL,
  `object_id` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `object_class` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `version` int(11) NOT NULL,
  `data` longtext COLLATE utf8_unicode_ci COMMENT '(DC2Type:array)',
  `username` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `log_class_lookup_idx` (`object_class`),
  KEY `log_date_lookup_idx` (`logged_at`),
  KEY `log_user_lookup_idx` (`username`),
  KEY `log_version_lookup_idx` (`object_id`,`object_class`,`version`)
) ENGINE=InnoDB AUTO_INCREMENT=4237446 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

QUERY

(please see the updated query in the next file version.md), this query doesn't use index.

SELECT * FROM ext_log_entries e0_ WHERE e0_.object_id = 37 AND e0_.object_class = 'Hypebeast\\Bundle\\CoreBundle\\Entity\\Order'

EXPLAIN

{
	"id": 1,
	"select_type": "SIMPLE",
	"table": "e0_",
	"type": "ALL",
	"possible_keys": "log_version_lookup_idx",
	"key": null,
	"key_len": null,
	"ref": null,
	"rows": 4706769,
	"Extra": "Using where"
}

version.md

Query

SELECT * FROM ext_log_entries e0_ WHERE e0_.object_id = 37 AND e0_.object_class = 'Hypebeast\\Bundle\\CoreBundle\\Entity\\Order' ORDER BY e0_.version DESC;

Explain

{
	"data":
	[
		{
			"id": 1,
			"select_type": "SIMPLE",
			"table": "e0_",
			"type": "ref",
			"possible_keys": "log_class_lookup_idx,log_version_lookup_idx,object_class",
			"key": "log_class_lookup_idx",
			"key_len": "767",
			"ref": "const",
			"rows": 1919578,
			"Extra": "Using index condition; Using where; Using filesort"
		}
	]
}