<html>
<head>
<title>Test Results</title>
<style>
td.winner { background-color: lightgreen; }
td.loser { background-color: lightyellow; }
td.mismatch { background-color: lightred; }
th { text-align: left; }
</style>
</head>
<body>
<h1>Test Results</h1>
<h2>Summary</h2>
<table border="1">
<tr>
<th>Test</th>
<th>Real</th>
<th>Distributed</th>
<th>Real Distributed</th>
<th>Count</th>
</tr>
<tr>
<td>
<a href="#1 str">1 str</a>
</td>
<td class="winner">
~0.8253178
</td>
<td class="loser">
~2.9416098
</td>
<td></td>
<td class="">
4822
</td>
</tr>
<tr>
<td>
<a href="#1 str w/index">1 str w/index</a>
</td>
<td class="winner">
~0.0775282
</td>
<td class="loser">
~0.7841422
</td>
<td></td>
<td class="">
1768
</td>
</tr>
<tr>
<td>
<a href="#1 str bzlike">1 str bzlike</a>
</td>
<td class="loser">
~0.797176
</td>
<td class="loser">
~6.4048538
</td>
<td class="winner">
~0.3302684
</td>
<td class="">
4822
</td>
</tr>
<tr>
<td>
<a href="#1 str bzlike w/index">1 str bzlike w/index</a>
</td>
<td class="winner">
~0.0774346
</td>
<td class="loser">
~0.7752752
</td>
<td class="loser">
~0.0814796
</td>
<td class="">
1768
</td>
</tr>
<tr>
<td>
<a href="#2 str">2 str</a>
</td>
<td class="winner">
~0.8105812
</td>
<td class="loser">
~2.9428318
</td>
<td></td>
<td class="">
75
</td>
</tr>
<tr>
<td>
<a href="#2 str w/index">2 str w/index</a>
</td>
<td class="winner">
~0.0872492
</td>
<td class="loser">
~0.812635
</td>
<td></td>
<td class="">
7
</td>
</tr>
<tr>
<td>
<a href="#3 str">3 str</a>
</td>
<td class="winner">
~0.8160316
</td>
<td class="loser">
~2.9259016
</td>
<td></td>
<td class="">
1
</td>
</tr>
<tr>
<td>
<a href="#3 str w/index">3 str w/index</a>
</td>
<td class="winner">
~0.0869828
</td>
<td class="loser">
~2.5796422
</td>
<td></td>
<td class="">
0
</td>
</tr>
<tr>
<td>
<a href="#10 str">10 str</a>
</td>
<td class="winner">
~2.3976604
</td>
<td class="loser">
~2.995392
</td>
<td></td>
<td class="">
0
</td>
</tr>
<tr>
<td>
<a href="#10 str w/index">10 str w/index</a>
</td>
<td class="winner">
~0.0869124
</td>
<td class="loser">
~0.813536
</td>
<td></td>
<td class="">
0
</td>
</tr>
<tr>
<td>
<a href="#1 int bzlike">1 int bzlike</a>
</td>
<td class="winner">
~0.7298874
</td>
<td class="loser">
~1.8679532
</td>
<td></td>
<td class="">
149985
</td>
</tr>
<tr>
<td>
<a href="#1 str, 1 int">1 str, 1 int</a>
</td>
<td class="winner">
~0.8092326
</td>
<td class="loser">
~3.689246
</td>
<td></td>
<td class="">
2407
</td>
</tr>
<tr>
<td>
<a href="#1 str, 1 int w/index">1 str, 1 int w/index</a>
</td>
<td class="winner">
~0.0782066
</td>
<td class="loser">
~0.7906608
</td>
<td></td>
<td class="">
867
</td>
</tr>
<tr>
<td>
<a href="#2 str, 2 int">2 str, 2 int</a>
</td>
<td class="winner">
~0.8087254
</td>
<td class="loser">
~3.9433596
</td>
<td></td>
<td class="">
20
</td>
</tr>
<tr>
<td>
<a href="#2 str, 2 int w/index">2 str, 2 int w/index</a>
</td>
<td class="winner">
~0.087214
</td>
<td class="loser">
~0.906494
</td>
<td></td>
<td class="">
4
</td>
</tr>
<tr>
<td>
<a href="#simple b.m.o query">simple b.m.o query</a>
</td>
<td class="loser">
~0.7475158
</td>
<td class="loser">
~0.322674
</td>
<td class="winner">
~0.0305798
</td>
<td class="">
27
</td>
</tr>
<tr>
<td>
<a href="#simple b.m.o query w/index">simple b.m.o query w/index</a>
</td>
<td class="winner">
~0.002554
</td>
<td class="loser">
~2.4971254
</td>
<td class="loser">
~0.0035594
</td>
<td class="">
27
</td>
</tr>
</table>
<h2><a name="1 str">1 str</a></h2>
<p></p>
<h3>Real</h3>
<p>Query: SELECT COUNT(*) FROM realcol WHERE INSTR(cf_aa, 'meta')</p>
<p>Times: 0.822847, 0.822323, 0.823333, 0.829587, 0.826015, 0.825331</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>ALL</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>300000</td>
<td>Using where</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT COUNT(*) FROM distcol_str WHERE field_id = 0 AND INSTR(value, 'meta')</p>
<p>Times: 3.003724, 2.932131, 3.062891, 2.818377, 2.813373, 3.081277</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>distcol_str</td>
<td>ref</td>
<td>field_id</td>
<td>field_id</td>
<td>4</td>
<td>const</td>
<td>253854</td>
<td>Using where</td>
</tr>
</table>
<h2><a name="1 str w/index">1 str w/index</a></h2>
<p>like the standard one-string query, but it searches with 'MATCH... AGAINST', which uses a fulltext index when possible</p>
<h3>Real</h3>
<p>Query: SELECT COUNT(*) FROM realcol WHERE MATCH(cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE)</p>
<p>Times: 0.078172, 0.077759, 0.077496, 0.077566, 0.077326, 0.077494</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>fulltext</td>
<td>cf_aa</td>
<td>cf_aa</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT COUNT(*) FROM distcol_str WHERE field_id = 0 AND MATCH(value) AGAINST ('+meta*' IN BOOLEAN MODE)</p>
<p>Times: 0.768075, 0.760558, 0.760808, 0.761046, 0.761423, 0.876876</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>distcol_str</td>
<td>fulltext</td>
<td>field_id,value</td>
<td>value</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h2><a name="1 str bzlike">1 str bzlike</a></h2>
<p>like the one-string query, but it joins tables in the distributed and real distributed queries just like Bugzilla would</p>
<h3>Real</h3>
<p>Query: SELECT COUNT(*) FROM realcol WHERE INSTR(cf_aa, 'meta')</p>
<p>Times: 0.825237, 0.796707, 0.79727, 0.796135, 0.796993, 0.798775</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>ALL</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>300000</td>
<td>Using where</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT COUNT(*) FROM realcol JOIN distcol_str on realcol.bug_id = distcol_str.bug_id WHERE field_id = 0 AND INSTR(value, 'meta')</p>
<p>Times: 4.728941, 12.193037, 4.740993, 5.750378, 4.613705, 4.726156</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>index</td>
<td>PRIMARY</td>
<td>PRIMARY</td>
<td>4</td>
<td></td>
<td>300000</td>
<td>Using index</td>
</tr>
<tr>
<td>distcol_str</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>realcol.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h3>Real Distributed</h3>
<p>Query: SELECT COUNT(*) FROM realcol JOIN cf_aa ON realcol.bug_id = cf_aa.bug_id WHERE INSTR(cf_aa.cf_aa, 'meta')</p>
<p>Times: 0.342014, 0.330449, 0.330031, 0.33019, 0.330405, 0.330267</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>cf_aa</td>
<td>ALL</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>300000</td>
<td>Using where</td>
</tr>
<tr>
<td>realcol</td>
<td>eq_ref</td>
<td>PRIMARY</td>
<td>PRIMARY</td>
<td>4</td>
<td>cf_aa.bug_id</td>
<td>1</td>
<td>Using index</td>
</tr>
</table>
<h2><a name="1 str bzlike w/index">1 str bzlike w/index</a></h2>
<p>like the one-string query with index, but it joins tables in the distributed and real distributed queries just like Bugzilla would</p>
<h3>Real</h3>
<p>Query: SELECT COUNT(*) FROM realcol WHERE MATCH(cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE)</p>
<p>Times: 0.077693, 0.077456, 0.077314, 0.077434, 0.077495, 0.077474</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>fulltext</td>
<td>cf_aa</td>
<td>cf_aa</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT COUNT(*) FROM realcol JOIN distcol_str ON realcol.bug_id = distcol_str.bug_id WHERE field_id = 0 AND MATCH(value) AGAINST ('+meta*' IN BOOLEAN MODE)</p>
<p>Times: 0.792291, 0.775686, 0.775466, 0.775542, 0.775533, 0.774149</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>distcol_str</td>
<td>fulltext</td>
<td>PRIMARY,field_id,value</td>
<td>value</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>realcol</td>
<td>eq_ref</td>
<td>PRIMARY</td>
<td>PRIMARY</td>
<td>4</td>
<td>distcol_str.bug_id</td>
<td>1</td>
<td>Using index</td>
</tr>
</table>
<h3>Real Distributed</h3>
<p>Query: SELECT COUNT(*) FROM realcol JOIN cf_aa ON realcol.bug_id = cf_aa.bug_id WHERE MATCH(cf_aa.cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE)</p>
<p>Times: 0.081753, 0.081695, 0.081352, 0.081505, 0.081353, 0.081493</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>cf_aa</td>
<td>fulltext</td>
<td>cf_aa</td>
<td>cf_aa</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>realcol</td>
<td>eq_ref</td>
<td>PRIMARY</td>
<td>PRIMARY</td>
<td>4</td>
<td>cf_aa.bug_id</td>
<td>1</td>
<td>Using index</td>
</tr>
</table>
<h2><a name="2 str">2 str</a></h2>
<p></p>
<h3>Real</h3>
<p>Query: SELECT COUNT(*) FROM realcol WHERE INSTR(cf_aa, 'meta') AND INSTR(cf_ab, 'meta')</p>
<p>Times: 0.81412, 0.810466, 0.81037, 0.810676, 0.811368, 0.810026</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>ALL</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>300000</td>
<td>Using where</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT COUNT(*) FROM distcol_str d1, distcol_str d2 WHERE d1.bug_id = d2.bug_id AND d1.field_id =0 AND d2.field_id = 1 AND INSTR(d1.value, 'meta') AND INSTR(d2.value, 'meta')</p>
<p>Times: 3.032694, 3.036032, 2.901595, 2.900205, 2.948728, 2.927599</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>d1</td>
<td>ref</td>
<td>PRIMARY,field_id</td>
<td>field_id</td>
<td>4</td>
<td>const</td>
<td>253854</td>
<td>Using where</td>
</tr>
<tr>
<td>d2</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d1.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h2><a name="2 str w/index">2 str w/index</a></h2>
<p></p>
<h3>Real</h3>
<p>Query: SELECT COUNT(*) FROM realcol WHERE MATCH(cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ab) AGAINST ('+meta*' IN BOOLEAN MODE)</p>
<p>Times: 0.087279, 0.087139, 0.08756, 0.087509, 0.086952, 0.087086</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>fulltext</td>
<td>cf_aa,cf_ab</td>
<td>cf_aa</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT COUNT(*) FROM distcol_str d1, distcol_str d2 WHERE d1.bug_id = d2.bug_id AND d1.field_id =0 AND d2.field_id = 1 AND MATCH(d1.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d2.value) AGAINST ('+meta*' IN BOOLEAN MODE)</p>
<p>Times: 0.805523, 0.817308, 0.807341, 0.826959, 0.804331, 0.807236</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>d1</td>
<td>fulltext</td>
<td>PRIMARY,field_id,value</td>
<td>value</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d2</td>
<td>eq_ref</td>
<td>PRIMARY,field_id,value</td>
<td>PRIMARY</td>
<td>8</td>
<td>d1.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h2><a name="3 str">3 str</a></h2>
<p></p>
<h3>Real</h3>
<p>Query: SELECT COUNT(*) FROM realcol WHERE INSTR(cf_aa, 'meta') AND INSTR(cf_ab, 'meta') AND INSTR(cf_ac, 'meta')</p>
<p>Times: 0.814271, 0.814165, 0.823215, 0.814527, 0.813628, 0.814623</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>ALL</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>300000</td>
<td>Using where</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT COUNT(*) FROM distcol_str d1, distcol_str d2, distcol_str d3 WHERE d1.bug_id = d2.bug_id AND d2.bug_id = d3.bug_id AND d1.field_id = 0 and d2.field_id = 1 AND d3.field_id = 2 and INSTR(d1.value, 'meta') AND INSTR(d2.value, 'meta') AND INSTR(d3.value, 'meta')</p>
<p>Times: 2.912995, 2.917432, 2.931658, 2.912653, 2.945861, 2.921904</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>d1</td>
<td>ref</td>
<td>PRIMARY,field_id</td>
<td>field_id</td>
<td>4</td>
<td>const</td>
<td>253854</td>
<td>Using where</td>
</tr>
<tr>
<td>d2</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d1.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d3</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d2.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h2><a name="3 str w/index">3 str w/index</a></h2>
<p></p>
<h3>Real</h3>
<p>Query: SELECT COUNT(*) FROM realcol WHERE MATCH(cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ab) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ac) AGAINST ('+meta*' IN BOOLEAN MODE)</p>
<p>Times: 0.087194, 0.087011, 0.086996, 0.087032, 0.086897, 0.086978</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>fulltext</td>
<td>cf_aa,cf_ab,cf_ac</td>
<td>cf_aa</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT COUNT(*) FROM distcol_str d1, distcol_str d2, distcol_str d3 WHERE d1.bug_id = d2.bug_id AND d2.bug_id = d3.bug_id AND d1.field_id = 0 and d2.field_id = 1 AND d3.field_id = 2 and MATCH(d1.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d2.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d3.value) AGAINST ('+meta*' IN BOOLEAN MODE)</p>
<p>Times: 0.805832, 0.94444, 0.888402, 9.325484, 0.866791, 0.873094</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>d1</td>
<td>fulltext</td>
<td>PRIMARY,field_id,value</td>
<td>value</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d2</td>
<td>eq_ref</td>
<td>PRIMARY,field_id,value</td>
<td>PRIMARY</td>
<td>8</td>
<td>d1.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d3</td>
<td>eq_ref</td>
<td>PRIMARY,field_id,value</td>
<td>PRIMARY</td>
<td>8</td>
<td>d2.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h2><a name="10 str">10 str</a></h2>
<p></p>
<h3>Real</h3>
<p>Query: SELECT COUNT(*) FROM realcol WHERE INSTR(cf_aa, 'meta') AND INSTR(cf_ab, 'meta') AND INSTR(cf_ac, 'meta') AND INSTR(cf_ad, 'meta') AND INSTR(cf_ae, 'meta') AND INSTR(cf_af, 'meta') AND INSTR(cf_ag, 'meta') AND INSTR(cf_ah, 'meta') AND INSTR(cf_ai, 'meta') AND INSTR(cf_aj, 'meta')</p>
<p>Times: 0.863672, 0.849517, 8.590269, 0.851656, 0.846636, 0.850224</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>ALL</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>300000</td>
<td>Using where</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT COUNT(*) FROM distcol_str d1, distcol_str d2, distcol_str d3, distcol_str d4, distcol_str d5, distcol_str d6, distcol_str d7, distcol_str d8, distcol_str d9, distcol_str d10 WHERE d1.bug_id = d2.bug_id AND d2.bug_id = d3.bug_id AND d3.bug_id = d4.bug_id AND d4.bug_id = d5.bug_id AND d5.bug_id = d6.bug_id AND d6.bug_id = d7.bug_id AND d7.bug_id = d8.bug_id AND d8.bug_id = d9.bug_id AND d9.bug_id = d10.bug_id AND d1.field_id = 0 AND d2.field_id = 1 AND d3.field_id = 2 AND d4.field_id = 3 AND d5.field_id = 4 AND d6.field_id = 5 AND d7.field_id = 6 AND d8.field_id = 7 AND d9.field_id = 8 AND d10.field_id = 9 AND INSTR(d1.value, 'meta') AND INSTR(d2.value, 'meta') AND INSTR(d3.value, 'meta') AND INSTR(d4.value, 'meta') AND INSTR(d5.value, 'meta') AND INSTR(d6.value, 'meta') AND INSTR(d7.value, 'meta') AND INSTR(d8.value, 'meta') AND INSTR(d9.value, 'meta') AND INSTR(d10.value, 'meta')</p>
<p>Times: 9.978837, 2.926238, 3.120614, 2.933706, 3.07024, 2.926162</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>d1</td>
<td>ref</td>
<td>PRIMARY,field_id</td>
<td>field_id</td>
<td>4</td>
<td>const</td>
<td>253854</td>
<td>Using where</td>
</tr>
<tr>
<td>d2</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d1.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d3</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d2.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d4</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d3.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d5</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d4.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d6</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d5.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d7</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d6.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d8</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d7.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d9</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d8.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d10</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d9.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h2><a name="10 str w/index">10 str w/index</a></h2>
<p></p>
<h3>Real</h3>
<p>Query: SELECT COUNT(*) FROM realcol WHERE MATCH(cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ab) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ac) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ad) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ae) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_af) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ag) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ah) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ai) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_aj) AGAINST ('+meta*' IN BOOLEAN MODE)</p>
<p>Times: 0.087089, 0.086755, 0.08691, 0.086902, 0.087096, 0.0868989999999999</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>fulltext</td>
<td>cf_aa,cf_ab,cf_ac,cf_ad,cf_ae,cf_af,cf_ag,cf_ah,cf_ai,cf_aj</td>
<td>cf_aa</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT COUNT(*) FROM distcol_str d1, distcol_str d2, distcol_str d3, distcol_str d4, distcol_str d5, distcol_str d6, distcol_str d7, distcol_str d8, distcol_str d9, distcol_str d10 WHERE d1.bug_id = d2.bug_id AND d2.bug_id = d3.bug_id AND d3.bug_id = d4.bug_id AND d4.bug_id = d5.bug_id AND d5.bug_id = d6.bug_id AND d6.bug_id = d7.bug_id AND d7.bug_id = d8.bug_id AND d8.bug_id = d9.bug_id AND d9.bug_id = d10.bug_id AND d1.field_id = 0 AND d2.field_id = 1 AND d3.field_id = 2 AND d4.field_id = 3 AND d5.field_id = 4 AND d6.field_id = 5 AND d7.field_id = 6 AND d8.field_id = 7 AND d9.field_id = 8 AND d10.field_id = 9 AND MATCH(d1.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d2.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d3.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d4.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d5.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d6.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d7.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d8.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d9.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d10.value) AGAINST ('+meta*' IN BOOLEAN MODE)</p>
<p>Times: 0.805598, 0.804368, 0.803882, 0.805245, 0.849252, 0.804933</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>d1</td>
<td>fulltext</td>
<td>PRIMARY,field_id,value</td>
<td>value</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d2</td>
<td>eq_ref</td>
<td>PRIMARY,field_id,value</td>
<td>PRIMARY</td>
<td>8</td>
<td>d1.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d3</td>
<td>eq_ref</td>
<td>PRIMARY,field_id,value</td>
<td>PRIMARY</td>
<td>8</td>
<td>d2.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d4</td>
<td>eq_ref</td>
<td>PRIMARY,field_id,value</td>
<td>PRIMARY</td>
<td>8</td>
<td>d3.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d5</td>
<td>eq_ref</td>
<td>PRIMARY,field_id,value</td>
<td>PRIMARY</td>
<td>8</td>
<td>d4.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d6</td>
<td>eq_ref</td>
<td>PRIMARY,field_id,value</td>
<td>PRIMARY</td>
<td>8</td>
<td>d5.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d7</td>
<td>eq_ref</td>
<td>PRIMARY,field_id,value</td>
<td>PRIMARY</td>
<td>8</td>
<td>d6.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d8</td>
<td>eq_ref</td>
<td>PRIMARY,field_id,value</td>
<td>PRIMARY</td>
<td>8</td>
<td>d7.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d9</td>
<td>eq_ref</td>
<td>PRIMARY,field_id,value</td>
<td>PRIMARY</td>
<td>8</td>
<td>d8.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d10</td>
<td>eq_ref</td>
<td>PRIMARY,field_id,value</td>
<td>PRIMARY</td>
<td>8</td>
<td>d9.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h2><a name="1 int bzlike">1 int bzlike</a></h2>
<p></p>
<h3>Real</h3>
<p>Query: SELECT COUNT(*) FROM realcol WHERE cf_int1 > 500</p>
<p>Times: 0.727732, 0.727864, 0.728028, 0.737366, 0.727551, 0.728628</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>ALL</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>300000</td>
<td>Using where</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT COUNT(*) FROM realcol JOIN distcol_int d2 ON realcol.bug_id = d2.bug_id WHERE d2.field_id = 10 AND d2.value > 500</p>
<p>Times: 1.884561, 1.871583, 1.862752, 1.863461, 1.872852, 1.869118</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>d2</td>
<td>ref</td>
<td>PRIMARY,field_id</td>
<td>field_id</td>
<td>4</td>
<td>const</td>
<td>316832</td>
<td>Using where</td>
</tr>
<tr>
<td>realcol</td>
<td>eq_ref</td>
<td>PRIMARY</td>
<td>PRIMARY</td>
<td>4</td>
<td>d2.bug_id</td>
<td>1</td>
<td>Using index</td>
</tr>
</table>
<h2><a name="1 str, 1 int">1 str, 1 int</a></h2>
<p></p>
<h3>Real</h3>
<p>Query: SELECT COUNT(*) FROM realcol WHERE INSTR(cf_aa, 'meta') AND cf_int1 > 500</p>
<p>Times: 0.807444, 0.807129, 0.81549, 0.807863, 0.807922, 0.807759</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>ALL</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>300000</td>
<td>Using where</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT COUNT(*) FROM distcol_str d1, distcol_int d2 WHERE d1.bug_id = d2.bug_id AND d1.field_id = 0 AND d2.field_id = 10 AND INSTR(d1.value, 'meta') AND d2.value > 500</p>
<p>Times: 3.673889, 3.695825, 3.667364, 3.685407, 3.695088, 3.702546</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>d2</td>
<td>ref</td>
<td>PRIMARY,field_id</td>
<td>field_id</td>
<td>4</td>
<td>const</td>
<td>316832</td>
<td>Using where</td>
</tr>
<tr>
<td>d1</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d2.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h2><a name="1 str, 1 int w/index">1 str, 1 int w/index</a></h2>
<p></p>
<h3>Real</h3>
<p>Query: SELECT COUNT(*) FROM realcol WHERE MATCH(cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE) AND cf_int1 > 500</p>
<p>Times: 0.078441, 0.078021, 0.07822, 0.078264, 0.078276, 0.078252</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>fulltext</td>
<td>cf_aa</td>
<td>cf_aa</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT COUNT(*) FROM distcol_str d1, distcol_int d2 WHERE d1.bug_id = d2.bug_id AND d1.field_id = 0 AND d2.field_id = 10 AND MATCH(d1.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND d2.value > 500</p>
<p>Times: 0.803839, 0.785457, 0.78563, 0.791925, 0.785736, 0.804556</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>d1</td>
<td>fulltext</td>
<td>PRIMARY,field_id,value</td>
<td>value</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d2</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d1.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h2><a name="2 str, 2 int">2 str, 2 int</a></h2>
<p></p>
<h3>Real</h3>
<p>Query: SELECT COUNT(*) FROM realcol WHERE INSTR(cf_aa, 'meta') AND instr(cf_ab, 'meta') AND cf_int1 > 500 AND cf_int2 > 500</p>
<p>Times: 0.808658, 0.8088, 0.808772, 0.808465, 0.807043, 0.810547</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>ALL</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>300000</td>
<td>Using where</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT COUNT(*) FROM distcol_str d1, distcol_str d2, distcol_int d3, distcol_int d4 WHERE d1.bug_id = d2.bug_id AND d2.bug_id = d3.bug_id AND d3.bug_id = d4.bug_id AND d1.field_id = 0 AND d2.field_id = 1 AND d3.field_id = 10 AND d4.field_id = 11 AND INSTR(d1.value, 'meta') AND INSTR(d2.value, 'meta') AND d3.value > 500 AND d4.value > 500</p>
<p>Times: 3.874831, 3.847627, 3.844531, 3.837327, 3.827565, 4.359748</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>d4</td>
<td>ref</td>
<td>PRIMARY,field_id</td>
<td>field_id</td>
<td>4</td>
<td>const</td>
<td>283167</td>
<td>Using where</td>
</tr>
<tr>
<td>d3</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d4.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d2</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d3.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d1</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d2.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h2><a name="2 str, 2 int w/index">2 str, 2 int w/index</a></h2>
<p></p>
<h3>Real</h3>
<p>Query: SELECT COUNT(*) FROM realcol WHERE MATCH(cf_aa) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(cf_ab) AGAINST ('+meta*' IN BOOLEAN MODE) AND cf_int1 > 500 AND cf_int2 > 500</p>
<p>Times: 0.087459, 0.087238, 0.087241, 0.087235, 0.087118, 0.087238</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>realcol</td>
<td>fulltext</td>
<td>cf_aa,cf_ab</td>
<td>cf_aa</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT COUNT(*) FROM distcol_str d1, distcol_str d2, distcol_int d3, distcol_int d4 WHERE d1.bug_id = d2.bug_id AND d2.bug_id = d3.bug_id AND d3.bug_id = d4.bug_id AND d1.field_id = 0 AND d2.field_id = 1 AND d3.field_id = 10 AND d4.field_id = 11 AND MATCH(d1.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND MATCH(d2.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND d3.value > 500 AND d4.value > 500</p>
<p>Times: 0.843591, 0.955776, 0.827126, 0.871107, 1.070281, 0.80818</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>d1</td>
<td>fulltext</td>
<td>PRIMARY,field_id,value</td>
<td>value</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d2</td>
<td>eq_ref</td>
<td>PRIMARY,field_id,value</td>
<td>PRIMARY</td>
<td>8</td>
<td>d1.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d3</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d2.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>d4</td>
<td>eq_ref</td>
<td>PRIMARY,field_id</td>
<td>PRIMARY</td>
<td>8</td>
<td>d3.bug_id,const</td>
<td>1</td>
<td>Using where</td>
</tr>
</table>
<h2><a name="simple b.m.o query">simple b.m.o query</a></h2>
<p></p>
<h3>Real</h3>
<p>Query: SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id WHERE bugs.assigned_to = map_assigned_to.userid AND ((INSTR(LOWER(bugs.status_whiteboard), 'meta'))) AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id ORDER BY bugs.bug_id</p>
<p>Times: 0.916029, 0.746673, 0.748565, 0.746871, 0.748482, 0.746988</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>bugs</td>
<td>ALL</td>
<td>assigned_to</td>
<td></td>
<td></td>
<td></td>
<td>278931</td>
<td>Using where; Using temporary; Using filesort</td>
</tr>
<tr>
<td>map_assigned_to</td>
<td>eq_ref</td>
<td>PRIMARY</td>
<td>PRIMARY</td>
<td>3</td>
<td>bugs.assigned_to</td>
<td>1</td>
<td></td>
</tr>
<tr>
<td>bug_group_map</td>
<td>ref</td>
<td>bug_id</td>
<td>bug_id</td>
<td>3</td>
<td>bugs.bug_id</td>
<td>16</td>
<td>Using where; Using index; Not exists</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id JOIN distcol_str ON bugs.bug_id = distcol_str.bug_id WHERE bugs.assigned_to = map_assigned_to.userid AND distcol_str.field_id = 11 AND ((INSTR(LOWER(distcol_str.value), 'meta'))) AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id ORDER BY bugs.bug_id</p>
<p>Times: 0.306999, 0.411835, 0.299967, 0.304957, 0.294088, 0.302523</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>distcol_str</td>
<td>ref</td>
<td>PRIMARY,field_id</td>
<td>field_id</td>
<td>4</td>
<td>const</td>
<td>86968</td>
<td>Using where; Using temporary; Using filesort</td>
</tr>
<tr>
<td>bugs</td>
<td>eq_ref</td>
<td>PRIMARY,assigned_to</td>
<td>PRIMARY</td>
<td>3</td>
<td>distcol_str.bug_id</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>map_assigned_to</td>
<td>eq_ref</td>
<td>PRIMARY</td>
<td>PRIMARY</td>
<td>3</td>
<td>bugs.assigned_to</td>
<td>1</td>
<td></td>
</tr>
<tr>
<td>bug_group_map</td>
<td>ref</td>
<td>bug_id</td>
<td>bug_id</td>
<td>3</td>
<td>bugs.bug_id</td>
<td>16</td>
<td>Using where; Using index; Not exists</td>
</tr>
</table>
<h3>Real Distributed</h3>
<p>Query: SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id JOIN status_whiteboard ON bugs.bug_id = status_whiteboard.bug_id WHERE bugs.assigned_to = map_assigned_to.userid AND ((INSTR(LOWER(status_whiteboard.status_whiteboard), 'meta'))) AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id ORDER BY bugs.bug_id</p>
<p>Times: 0.030708, 0.030641, 0.030573, 0.030537, 0.030583, 0.030565</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>status_whiteboard</td>
<td>ALL</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td>33094</td>
<td>Using where; Using temporary; Using filesort</td>
</tr>
<tr>
<td>bugs</td>
<td>eq_ref</td>
<td>PRIMARY,assigned_to</td>
<td>PRIMARY</td>
<td>3</td>
<td>status_whiteboard.bug_id</td>
<td>1</td>
<td></td>
</tr>
<tr>
<td>map_assigned_to</td>
<td>eq_ref</td>
<td>PRIMARY</td>
<td>PRIMARY</td>
<td>3</td>
<td>bugs.assigned_to</td>
<td>1</td>
<td></td>
</tr>
<tr>
<td>bug_group_map</td>
<td>ref</td>
<td>bug_id</td>
<td>bug_id</td>
<td>3</td>
<td>bugs.bug_id</td>
<td>16</td>
<td>Using where; Using index; Not exists</td>
</tr>
</table>
<h2><a name="simple b.m.o query w/index">simple b.m.o query w/index</a></h2>
<p></p>
<h3>Real</h3>
<p>Query: SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id WHERE bugs.assigned_to = map_assigned_to.userid AND MATCH(status_whiteboard) AGAINST ('+meta*' IN BOOLEAN MODE) AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id ORDER BY bugs.bug_id</p>
<p>Times: 0.002629, 0.002565, 0.00255, 0.002559, 0.002551, 0.002545</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>bugs</td>
<td>fulltext</td>
<td>assigned_to,status_whiteboard</td>
<td>status_whiteboard</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where; Using temporary; Using filesort</td>
</tr>
<tr>
<td>map_assigned_to</td>
<td>eq_ref</td>
<td>PRIMARY</td>
<td>PRIMARY</td>
<td>3</td>
<td>bugs.assigned_to</td>
<td>1</td>
<td></td>
</tr>
<tr>
<td>bug_group_map</td>
<td>ref</td>
<td>bug_id</td>
<td>bug_id</td>
<td>3</td>
<td>bugs.bug_id</td>
<td>16</td>
<td>Using where; Using index; Not exists</td>
</tr>
</table>
<h3>Distributed</h3>
<p>Query: SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id JOIN distcol_str ON bugs.bug_id = distcol_str.bug_id WHERE bugs.assigned_to = map_assigned_to.userid AND distcol_str.field_id = 11 AND MATCH(distcol_str.value) AGAINST ('+meta*' IN BOOLEAN MODE) AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id ORDER BY bugs.bug_id</p>
<p>Times: 0.779369, 0.768983, 0.770387, 0.766633, 9.359634, 0.81999</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>distcol_str</td>
<td>fulltext</td>
<td>PRIMARY,field_id,value</td>
<td>value</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where; Using temporary; Using filesort</td>
</tr>
<tr>
<td>bugs</td>
<td>eq_ref</td>
<td>PRIMARY,assigned_to</td>
<td>PRIMARY</td>
<td>3</td>
<td>distcol_str.bug_id</td>
<td>1</td>
<td>Using where</td>
</tr>
<tr>
<td>map_assigned_to</td>
<td>eq_ref</td>
<td>PRIMARY</td>
<td>PRIMARY</td>
<td>3</td>
<td>bugs.assigned_to</td>
<td>1</td>
<td></td>
</tr>
<tr>
<td>bug_group_map</td>
<td>ref</td>
<td>bug_id</td>
<td>bug_id</td>
<td>3</td>
<td>bugs.bug_id</td>
<td>16</td>
<td>Using where; Using index; Not exists</td>
</tr>
</table>
<h3>Real Distributed</h3>
<p>Query: SELECT bugs.bug_id, bugs.bug_severity, bugs.priority, bugs.bug_status, bugs.resolution, bugs.bug_severity, bugs.priority, bugs.rep_platform, map_assigned_to.login_name, bugs.bug_status, bugs.resolution, bugs.short_desc FROM bugs, profiles AS map_assigned_to LEFT JOIN bug_group_map ON bug_group_map.bug_id = bugs.bug_id JOIN status_whiteboard ON bugs.bug_id = status_whiteboard.bug_id WHERE bugs.assigned_to = map_assigned_to.userid AND MATCH(status_whiteboard.status_whiteboard) AGAINST ('+meta*' IN BOOLEAN MODE) AND ((bug_group_map.group_id IS NULL)) GROUP BY bugs.bug_id ORDER BY bugs.bug_id</p>
<p>Times: 0.003566, 0.003404, 0.0036, 0.003657, 0.003576, 0.00356</p>
<table border="1">
<caption>Explanation</caption>
<tr>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>status_whiteboard</td>
<td>fulltext</td>
<td>status_whiteboard</td>
<td>status_whiteboard</td>
<td>0</td>
<td></td>
<td>1</td>
<td>Using where; Using temporary; Using filesort</td>
</tr>
<tr>
<td>bugs</td>
<td>eq_ref</td>
<td>PRIMARY,assigned_to</td>
<td>PRIMARY</td>
<td>3</td>
<td>status_whiteboard.bug_id</td>
<td>1</td>
<td></td>
</tr>
<tr>
<td>map_assigned_to</td>
<td>eq_ref</td>
<td>PRIMARY</td>
<td>PRIMARY</td>
<td>3</td>
<td>bugs.assigned_to</td>
<td>1</td>
<td></td>
</tr>
<tr>
<td>bug_group_map</td>
<td>ref</td>
<td>bug_id</td>
<td>bug_id</td>
<td>3</td>
<td>bugs.bug_id</td>
<td>16</td>
<td>Using where; Using index; Not exists</td>
</tr>
</table>
</body>
</html>