Ben W. Brumfield benwbrum
benwbrum
/ test1_query_explain_commands.js
Last active
December 17, 2015 18:39
commands to run EXPLAIN PLANs on all queries executed in Test 1 up to May 26
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // Rails console command to generate this list: | |
| // SearchQuery.all.each { |q| print "db.search_records.find( #{q.search_params.to_json} ).explain();\n" };0 | |
| db.search_records.find( {"primary_names":{"$elemMatch":{"first_name":"james","last_name":"brumfield"}}} ).explain(); | |
| db.search_records.find( {"primary_names":{"$elemMatch":{"first_name":"james","last_name":"brumfield"}}} ).explain(); | |
| db.search_records.find( {"chapman_code":"BDF","primary_names":{"$elemMatch":{"last_name":"carlstead"}}} ).explain(); | |
| db.search_records.find( {"inclusive_names":{"$elemMatch":{"first_name":"william","last_name":"maxee"}}} ).explain(); | |
| db.search_records.find( {"primary_names":{"$elemMatch":{"first_name":"william","last_name":"smith"}}} ).explain(); | |
| db.search_records.find( {"chapman_code":"BDF","primary_names":{"$elemMatch":{"last_name":"carlstead"}}} ).explain(); | |
| db.search_records.find( {"inclusive_names":{"$elemMatch":{"first_name":"william","last_name":"towler"}}} ).explain(); |
benwbrum
/ test1_query_explain_plan_results.js
Last active
December 17, 2015 18:59
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| //> db.search_records.find( {"primary_names":{"$elemMatch":{"first_name":"james","last_name":"brumfield"}}} ).explain(); | |
| { | |
| "cursor" : "BtreeCursor primary_names.last_name_1_primary_names.first_name_1", | |
| "isMultiKey" : true, | |
| "n" : 7, | |
| "nscannedObjects" : 308, | |
| "nscanned" : 308, | |
| "nscannedObjectsAllPlans" : 308, | |
| "nscannedAllPlans" : 308, | |
| "scanAndOrder" : false, |
benwbrum
/ test1_query_performance.js
Created
May 28, 2013 18:55
Queries and their performance fromtest pass 1
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| > db.perf.find({},{query:1, "profile.nscannedObjects": 1, "profile.millis":1, "profile.cursor":1, "profile.n":1}).sort({"profile.nscannedObjects":-1}).pretty() | |
| { | |
| "_id" : ObjectId("51a4dd31f53d9190e72a683b"), | |
| "query" : "db.search_records.find( {\"record_type\":\"ma\",\"chapman_code\":\"LIN\",\"primary_names\":{\"$elemMatch\":{\"first_name\":\"snell\"}}} ).explain()", | |
| "profile" : { | |
| "cursor" : "BasicCursor", | |
| "n" : 0, | |
| "nscannedObjects" : 24020988, | |
| "millis" : 1022028 | |
| } |
benwbrum
/ test1_query_explain_plan_results_new_indexes.js
Last active
December 17, 2015 21:09
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| > db.search_records.find( {"primary_names":{"$elemMatch":{"first_name":"james","last_name":"brumfield"}}} ).explain(); | |
| { | |
| "cursor" : "BtreeCursor primary_names.last_name_1_primary_names.first_name_1", | |
| "isMultiKey" : true, | |
| "n" : 7, | |
| "nscannedObjects" : 308, | |
| "nscanned" : 308, | |
| "nscannedObjectsAllPlans" : 308, | |
| "nscannedAllPlans" : 308, | |
| "scanAndOrder" : false, |
benwbrum
/ test1_query_performance_new_indexes.js
Created
May 29, 2013 21:04
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| > db.perf2.find({},{query:1, "profile.nscannedObjects": 1, "profile.millis":1, "profile.cursor":1, "profile.n":1}).sort({"profile.nscannedObjects":-1}).pretty() | |
| { | |
| "_id" : ObjectId("51a66894448a5628ef2bac55"), | |
| "query" : "db.search_records.find( {\"primary_names\":{\"$elemMatch\":{\"first_name\":\"william\",\"last_name\":\"smith\"}}} ).explain()", | |
| "profile" : { | |
| "cursor" : "BtreeCursor primary_names_o_last_name_1_primary_names.first_name_1", | |
| "n" : 22690, | |
| "nscannedObjects" : 359863, | |
| "millis" : 4580996 |
benwbrum
/ mongo_orderby_bug.js
Created
June 5, 2013 17:14
Output of a query that appears to ignore $orderby
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| > db.search_records.find( { "$query" : { "primary_names" : { "$elemMatch" : { "first_name" : "william", "last_name" : "smith" } } }, $orderby: { "search_date" : 1 } }, {search_date:1}) | |
| { "_id" : ObjectId("519bbef6a020dd26fd00370f"), "search_date" : "1846-10-14" } | |
| { "_id" : ObjectId("51aaa56aa020dd1e05005656"), "search_date" : "1679-12-28" } | |
| { "_id" : ObjectId("51aaa56aa020dd1e0500565a"), "search_date" : "1680-01-01" } | |
| { "_id" : ObjectId("51aaa52ca020dd1e05002904"), "search_date" : "1738-07-25" } | |
| { "_id" : ObjectId("51aaa50da020dd1e050010bc"), "search_date" : "1743-02-27" } | |
| { "_id" : ObjectId("51aaa530a020dd1e05002c96"), "search_date" : "1758-06-14" } | |
| { "_id" : ObjectId("51aaa504a020dd1e050009f2"), "search_date" : "1768-01-07" } | |
| { "_id" : ObjectId("51aaa573a020dd1e05005da3"), "search_date" : "1791-08-01" } | |
| { "_id" : ObjectId("51aaa542a020dd1e05003b98"), "search_date" : "1795-11-21" } |
benwbrum
/ mongo_sort_issue_2.js
Created
June 7, 2013 11:26
Demonstration of interaction between elemmatch and orderby
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| > db.search_records.find( { "$query" : { "primary_names" : { "$elemMatch" : { "first_name" : "william", "last_name" : "smith" } } }, $orderby: { "search_date" : 1 } }, {search_date:1}) | |
| { "_id" : ObjectId("519bbef6a020dd26fd00370f"), "search_date" : "1846-10-14" } | |
| { "_id" : ObjectId("51aaa56aa020dd1e05005656"), "search_date" : "1679-12-28" } | |
| { "_id" : ObjectId("51aaa56aa020dd1e0500565a"), "search_date" : "1680-01-01" } | |
| { "_id" : ObjectId("51aaa52ca020dd1e05002904"), "search_date" : "1738-07-25" } | |
| { "_id" : ObjectId("51aaa50da020dd1e050010bc"), "search_date" : "1743-02-27" } | |
| { "_id" : ObjectId("51aaa530a020dd1e05002c96"), "search_date" : "1758-06-14" } | |
| { "_id" : ObjectId("51aaa504a020dd1e050009f2"), "search_date" : "1768-01-07" } | |
| { "_id" : ObjectId("51aaa573a020dd1e05005da3"), "search_date" : "1791-08-01" } | |
| { "_id" : ObjectId("51aaa542a020dd1e05003b98"), "search_date" : "1795-11-21" } |
benwbrum
/ mongodb_currentOps.js
Created
July 6, 2013 19:25
13m record slow-down -- operations running after performance dropped to 100K records/hour or less
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| > db.currentOp(); | |
| { | |
| "inprog" : [ | |
| { | |
| "opid" : 60432862, | |
| "active" : true, | |
| "secs_running" : 0, | |
| "op" : "insert", | |
| "ns" : "mv_20130601.search_records", | |
| "insert" : { |
benwbrum
/ surname sort.txt
Created
July 30, 2013 14:18
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| +-------------------------------------------------+ | |
| 911 rows in set (0.00 sec) | |
| mysql> select RIGHT(p.name, LOCATE(' ', REVERSE(p.name)) - 1) surname, name from NormalizedPerson p order by surname; | |
| +--------------+----------------------------------------------------+ | |
| | surname | name | | |
| +--------------+----------------------------------------------------+ | |
| | | Lafitte | | |
| | | Whitlock | | |
| | | Wheeler | |
benwbrum
/ dap_hacks.sh
Last active
December 20, 2015 11:59
Variously clever or horrifying hacks for the DAP project
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # SQL statement to pull all unreferenced recipients from the correspondence database | |
| # and generate a shell command grepping the TEI XML files for their identities: | |
| mysql --user=x --password=x --database=x --batch --execute="select concat('grep -A 2 \'type=\\\"recipient\' ', id, '.xml | head -2 | tail -1') from Document where sentFromPerson is null" > find_unknown_recipients.sh | |
| # sample output: | |
| # grep -A 2 'type="recipient' APB0042.xml | head -2 | tail -1 | |
| # grep -A 2 'type="recipient' APB0062.xml | head -2 | tail -1 | |
| # grep -A 2 'type="recipient' APB0072.xml | head -2 | tail -1 | |
| # grep -A 2 'type="recipient' APB0094.xml | head -2 | tail -1 |