P5.md

P5. For the same table in P4, find how many bugs were created on "2019-03-01" or later. Your query should produce a table with one column called "count". This problem is graded partially, 10% on correctness (your query gets the correct count) and 90% on performance (your query makes use of available indexes).

Scored 10% only

SHOW INDEXES FROM bugs;

"records": [
		{
			"Table": "bugs",
			"Non_unique": 0,
			"Key_name": "PRIMARY",
			"Seq_in_index": 1,
			"Column_name": "id",
			"Collation": "A",
			"Cardinality": 9791826,
			"Sub_part": null,
			"Packed": null,
			"Null": "",
			"Index_type": "BTREE",
			"Comment": "",
			"Index_comment": ""
		},
		{
			"Table": "bugs",
			"Non_unique": 1,
			"Key_name": "index_bugs_on_category_and_token_and_reported_at",
			"Seq_in_index": 1,
			"Column_name": "category",
			"Collation": "A",
			"Cardinality": 1,
			"Sub_part": null,
			"Packed": null,
			"Null": "YES",
			"Index_type": "BTREE",
			"Comment": "",
			"Index_comment": ""
		},
		{
			"Table": "bugs",
			"Non_unique": 1,
			"Key_name": "index_bugs_on_category_and_token_and_reported_at",
			"Seq_in_index": 2,
			"Column_name": "token",
			"Collation": "A",
			"Cardinality": 29946,
			"Sub_part": null,
			"Packed": null,
			"Null": "YES",
			"Index_type": "BTREE",
			"Comment": "",
			"Index_comment": ""
		},
		{
			"Table": "bugs",
			"Non_unique": 1,
			"Key_name": "index_bugs_on_category_and_token_and_reported_at",
			"Seq_in_index": 3,
			"Column_name": "reported_at",
			"Collation": "A",
			"Cardinality": 6085027,
			"Sub_part": null,
			"Packed": null,
			"Null": "YES",
			"Index_type": "BTREE",
			"Comment": "",
			"Index_comment": ""
		}
	]
}

SELECT COUNT(x.created_at) AS count 
FROM bugs AS y 
USE INDEX (PRIMARY,index_bugs_on_category_and_token_and_reported_at) 
INNER JOIN bugs AS x ON x.id=y.id 
WHERE x.created_at >= '2019-03-01