\r\nmysql> SELECT COUNT(test_spec) \r\n FROM test_result \r\n WHERE build_record=6742;\r\n+------------------+\r\n| COUNT(test_spec) |\r\n+------------------+\r\n| 111 |\r\n+------------------+\r\n1 row in set (4.12 sec)\r\n<\/pre>\n
\r\nmysql> EXPLAIN SELECT * \r\n FROM test_result \r\n WHERE build_record=6742;\r\n+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+\r\n| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |\r\n+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+\r\n| 1 | SIMPLE | test_result | ALL | NULL | NULL | NULL | NULL | 443099 | Using where |\r\n+----+-------------+-------------+------+---------------+------+---------+------+--------+-------------+\r\n<\/pre>\nFrom what I read about the foregoing data, a type of “ALL” is as bad as it gets. Apparently, it means that all rows were examined. Sure enough– all 443k rows had to be studied to come up with the query result. This makes sense from an algorithmic perspective– cover each row in a linear manner. How could this be improved using a rudimentary knowledge of algorithms and data structures? Since the query operates by studying rows based on build_record, how about making an ordered index of build_record numbers that point to their rows and perhaps doing a binary search on the index when build_record is part of the WHERE clause? In fact, MySQL has the same idea. I’m not sure of the exact technical underpinnings of the indexing mechanism, but this is how you ask MySQL to build and maintain an index:<\/p>\n
\r\nmysql> CREATE INDEX build_record_index \r\n ON test_result (build_record);\r\n<\/pre>\nWhat kind of improvement? Check out the explanation (using a different build record to avoid query caching):<\/p>\n
\r\nmysql> EXPLAIN SELECT * \r\n FROM test_result \r\n WHERE build_record=6745;\r\n+----+-------------+-------------+------+--------------------+--------------------+---------+-------+------+-------+\r\n| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |\r\n+----+-------------+-------------+------+--------------------+--------------------+---------+-------+------+-------+\r\n| 1 | SIMPLE | test_result | ref | build_record_index | build_record_index | 4 | const | 109 | |\r\n+----+-------------+-------------+------+--------------------+--------------------+---------+-------+------+-------+\r\n<\/pre>\nNice. Only 109 rows were inspected, which is kind of strange, since there are 111 rows in the query:<\/p>\n
\r\nmysql> SELECT COUNT(test_spec) \r\n FROM test_result \r\n WHERE build_record=6745;\r\n+------------------+\r\n| COUNT(test_spec) |\r\n+------------------+\r\n| 111 |\r\n+------------------+\r\n1 row in set (0.50 sec)\r\n<\/pre>\nAnd the average query shapes up to be well under a second.<\/p>\n
Anyway, I found it all terribly interesting. Further, certain aspects of FATE will be much faster now and will scale much better as the data set grows. Maybe someone else will find this article through Google and find this more practical example to be more helpful than the common employee salary database examples that litter the web.<\/p>\n","protected":false},"excerpt":{"rendered":"
I have a tendency to regard a database engine as a black box. I just formulate my queries and count on the engine to make them fast, somehow. I think this is similar to the faith that people tend to place in language compilers– just write the C code and the compiler will just magically […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[101],"tags":[],"class_list":["post-534","post","type-post","status-publish","format-standard","hentry","category-fate-server"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t