null+****@clear*****
null+****@clear*****
2010年 11月 24日 (水) 00:14:17 JST
Kentoku SHIBA 2010-11-23 15:14:17 +0000 (Tue, 23 Nov 2010)
New Revision: 8b4345cd60ee638ff00524ab311c18df418d6625
Log:
#445 ORDER BY LIMITの高速化
Added files:
test/sql/r/order_limit_performance.result
test/sql/t/order_limit_performance.test
Modified files:
ha_mroonga.cc
ha_mroonga.h
test/sql/t/count_performance.test
Modified: ha_mroonga.cc (+158 -8)
===================================================================
--- ha_mroonga.cc 2010-11-23 14:06:40 +0000 (e5730ec)
+++ ha_mroonga.cc 2010-11-23 15:14:17 +0000 (39a7d67)
@@ -63,6 +63,7 @@ handlerton *mrn_hton_ptr;
/* status */
st_mrn_statuses mrn_status_vals;
long mrn_count_skip = 0;
+long mrn_fast_order_limit = 0;
/* logging */
const char *mrn_logfile_name = MRN_LOG_FILE_NAME;
@@ -97,12 +98,14 @@ static void mrn_create_status()
{
DBUG_ENTER("mrn_create_status");
mrn_status_vals.count_skip = mrn_count_skip;
+ mrn_status_vals.fast_order_limit = mrn_fast_order_limit;
DBUG_VOID_RETURN;
}
struct st_mysql_show_var mrn_statuses[] =
{
{"count_skip", (char *) &mrn_status_vals.count_skip, SHOW_LONG},
+ {"fast_order_limit", (char *) &mrn_status_vals.fast_order_limit, SHOW_LONG},
{NullS, NullS, SHOW_LONG}
};
@@ -195,12 +198,12 @@ void last_insert_grn_id_deinit(UDF_INIT *initid)
/* Groonga information schema */
int GROONGA_VERSION_SHORT = 0x0001;
static const char plugin_author[] = "Yoshinori Matsunobu";
-static struct st_mysql_information_schema i_s_info =
+static struct st_mysql_information_schema i_s_info =
{
- MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION
+ MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION
};
-static ST_FIELD_INFO i_s_groonga_stats_fields_info[] =
+static ST_FIELD_INFO i_s_groonga_stats_fields_info[] =
{
{
"VERSION",
@@ -240,8 +243,8 @@ static int i_s_groonga_stats_deinit(void* p)
static int i_s_groonga_stats_fill(
THD* thd, TABLE_LIST* tables, COND* cond)
{
- TABLE* table = (TABLE *) tables->table;
- int status = 0;
+ TABLE* table = (TABLE *) tables->table;
+ int status = 0;
DBUG_ENTER("i_s_groonga_fill_low");
table->field[0]->store(grn_get_version(), strlen(grn_get_version()),
system_charset_info);
@@ -760,6 +763,8 @@ ha_mroonga::ha_mroonga(handlerton *hton, TABLE_SHARE *share)
grn_ctx_use(ctx, mrn_db);
cur = NULL;
res = NULL;
+ res0 = NULL;
+ sort_keys = NULL;
DBUG_VOID_RETURN;
}
@@ -2059,6 +2064,21 @@ FT_INFO *ha_mroonga::ft_init_ext(uint flags, uint keynr, String *key)
const char *keyword = key->ptr();
int keyword_size = key->length();
check_count_skip(0, 0, TRUE);
+ if (sort_keys != NULL) {
+ free(sort_keys);
+ sort_keys = NULL;
+ }
+ check_fast_order_limit();
+ if (res0 != NULL) {
+ grn_obj_unlink(ctx, res0);
+ res0 = NULL;
+ }
+ if (res != NULL) {
+ grn_obj_unlink(ctx, res);
+ _score = NULL;
+ res = NULL;
+ }
+
row_id = GRN_ID_NIL;
res = grn_table_create(ctx, NULL, 0, NULL,
@@ -2078,7 +2098,20 @@ FT_INFO *ha_mroonga::ft_init_ext(uint flags, uint keynr, String *key)
}
_score = grn_obj_column(ctx, res, MRN_SCORE_COL_NAME, strlen(MRN_SCORE_COL_NAME));
int n_rec = grn_table_size(ctx, res);
- cur = grn_table_cursor_open(ctx, res, NULL, 0, NULL, 0, 0, -1, 0);
+ if (!fast_order_limit) {
+ cur = grn_table_cursor_open(ctx, res, NULL, 0, NULL, 0, 0, -1, 0);
+ } else {
+ st_select_lex *select_lex = table->pos_in_table_list->select_lex;
+ res0 = grn_table_create(ctx, NULL, 0, NULL,
+ GRN_OBJ_TABLE_NO_KEY, NULL, res);
+ for (int i = 0; i < n_sort_keys; i++) {
+ if (!sort_keys[i].key) {
+ sort_keys[i].key = _score;
+ }
+ }
+ grn_table_sort(ctx, res, 0, limit, res0, sort_keys, n_sort_keys);
+ cur = grn_table_cursor_open(ctx, res0, NULL, 0, NULL, 0, 0, -1, 0);
+ }
{ // for "not match"
mrn_ft_info.please = &mrn_ft_vft;
@@ -2115,7 +2148,15 @@ int ha_mroonga::ft_read(uchar *buf)
DBUG_RETURN(0);
}
- grn_table_get_key(ctx, res, rid, &row_id, sizeof(grn_id));
+ if (!fast_order_limit) {
+ grn_table_get_key(ctx, res, rid, &row_id, sizeof(grn_id));
+ } else if (fast_order_limit_with_index) {
+ grn_table_get_key(ctx, res0, rid, &row_id, sizeof(grn_id));
+ } else {
+ grn_id rid2;
+ grn_table_get_key(ctx, res0, rid, &rid2, sizeof(grn_id));
+ grn_table_get_key(ctx, res, rid2, &row_id, sizeof(grn_id));
+ }
store_fields_from_primary_table(buf, row_id);
DBUG_RETURN(0);
}
@@ -2144,7 +2185,7 @@ void ha_mroonga::check_count_skip(key_part_map start_key_part_map,
key_part_map end_key_part_map, bool fulltext)
{
DBUG_ENTER("ha_mroonga::check_count_skip");
- st_select_lex *select_lex = table->pos_in_table_list->select_lex;
+ st_select_lex *select_lex = table->pos_in_table_list->select_lex;
if (
thd_sql_command(ha_thd()) == SQLCOM_SELECT &&
@@ -2230,6 +2271,107 @@ void ha_mroonga::check_count_skip(key_part_map start_key_part_map,
DBUG_VOID_RETURN;
}
+void ha_mroonga::check_fast_order_limit()
+{
+ DBUG_ENTER("ha_mroonga::check_fast_order_limit");
+ st_select_lex *select_lex = table->pos_in_table_list->select_lex;
+
+ if (
+ thd_sql_command(ha_thd()) == SQLCOM_SELECT &&
+ !select_lex->n_sum_items &&
+ !select_lex->group_list.elements &&
+ !select_lex->having &&
+ select_lex->table_list.elements == 1 &&
+ select_lex->order_list.elements &&
+ select_lex->explicit_limit &&
+ select_lex->select_limit &&
+ select_lex->select_limit->val_int() > 0
+ ) {
+ limit = (select_lex->offset_limit ?
+ select_lex->offset_limit->val_int() : 0) +
+ select_lex->select_limit->val_int();
+ if (limit > (longlong) INT_MAX) {
+ DBUG_PRINT("info",("mroonga fast_order_limit = FALSE"));
+ fast_order_limit = FALSE;
+ DBUG_VOID_RETURN;
+ }
+ Item *info = (Item *) select_lex->item_list.first_node()->info;
+ Item *where;
+ where = select_lex->where;
+ if (!where ||
+ where->type() != Item::FUNC_ITEM ||
+ ((Item_func *)where)->functype() != Item_func::FT_FUNC) {
+ DBUG_PRINT("info",("mroonga fast_order_limit = FALSE"));
+ fast_order_limit = FALSE;
+ DBUG_VOID_RETURN;
+ }
+ where = where->next;
+ if (!where ||
+ where->type() != Item::STRING_ITEM) {
+ DBUG_PRINT("info",("mroonga fast_order_limit = FALSE"));
+ fast_order_limit = FALSE;
+ DBUG_VOID_RETURN;
+ }
+ for (where = where->next; where; where = where->next) {
+ if (where->type() != Item::FIELD_ITEM || where == info)
+ break;
+ }
+ if (where && where != info) {
+ DBUG_PRINT("info",("mroonga fast_order_limit = FALSE"));
+ fast_order_limit = FALSE;
+ DBUG_VOID_RETURN;
+ }
+ n_sort_keys = select_lex->order_list.elements;
+ sort_keys = (grn_table_sort_key *) malloc(sizeof(grn_table_sort_key) *
+ n_sort_keys);
+ ORDER *order;
+ int i, col_field_index = -1;
+ for (order = (ORDER *) select_lex->order_list.first, i = 0; order;
+ order = order->next, i++) {
+ if ((*order->item)->type() != Item::FIELD_ITEM)
+ {
+ DBUG_PRINT("info",("mroonga fast_order_limit = FALSE"));
+ fast_order_limit = FALSE;
+ DBUG_VOID_RETURN;
+ }
+ Field *field = ((Item_field *) (*order->item))->field;
+ const char *col_name = field->field_name;
+ int col_name_size = strlen(col_name);
+
+ if (strncmp(MRN_ID_COL_NAME, col_name, col_name_size) == 0) {
+ sort_keys[i].key = grn_obj_column(ctx, tbl, col_name, col_name_size);
+ } else if (strncmp(MRN_SCORE_COL_NAME, col_name, col_name_size) == 0) {
+ sort_keys[i].key = NULL;
+ } else {
+ sort_keys[i].key = col[field->field_index];
+ col_field_index = field->field_index;
+ }
+ sort_keys[i].offset = 0;
+ if (order->asc)
+ sort_keys[i].flags = GRN_TABLE_SORT_ASC;
+ else
+ sort_keys[i].flags = GRN_TABLE_SORT_DESC;
+ }
+ grn_obj *index;
+ if (i == 1 && col_field_index >= 0 &&
+ grn_column_index(ctx, col[col_field_index], GRN_OP_LESS,
+ &index, 1, NULL)) {
+ DBUG_PRINT("info",("mroonga fast_order_limit_with_index = TRUE"));
+ fast_order_limit_with_index = TRUE;
+ } else {
+ DBUG_PRINT("info",("mroonga fast_order_limit_with_index = FALSE"));
+ fast_order_limit_with_index = FALSE;
+ }
+ DBUG_PRINT("info",("mroonga fast_order_limit = TRUE"));
+ fast_order_limit = TRUE;
+ mrn_fast_order_limit++;
+ DBUG_VOID_RETURN;
+ }
+ DBUG_PRINT("info",("mroonga fast_order_limit = FALSE"));
+ fast_order_limit = FALSE;
+ DBUG_VOID_RETURN;
+}
+
void ha_mroonga::store_fields_from_primary_table(uchar *buf, grn_id rid)
{
DBUG_ENTER("ha_mroonga::store_fields_from_primary_table");
@@ -2288,6 +2430,14 @@ void ha_mroonga::store_fields_from_primary_table(uchar *buf, grn_id rid)
int ha_mroonga::reset()
{
DBUG_ENTER("ha_mroonga::reset()");
+ if (sort_keys != NULL) {
+ free(sort_keys);
+ sort_keys = NULL;
+ }
+ if (res0 != NULL) {
+ grn_obj_unlink(ctx, res0);
+ res0 = NULL;
+ }
if (res != NULL) {
grn_obj_unlink(ctx, res);
_score = NULL;
Modified: ha_mroonga.h (+9 -0)
===================================================================
--- ha_mroonga.h 2010-11-23 14:06:40 +0000 (249cb6e)
+++ ha_mroonga.h 2010-11-23 15:14:17 +0000 (7e33d73)
@@ -35,6 +35,7 @@ extern "C" {
struct st_mrn_statuses
{
long count_skip;
+ long fast_order_limit;
};
struct st_mrn_ft_info
@@ -77,6 +78,7 @@ class ha_mroonga: public handler
grn_obj **idx_col;
grn_obj *res;
+ grn_obj *res0;
grn_table_cursor *cur;
grn_id row_id;
grn_obj *_score;
@@ -88,7 +90,13 @@ class ha_mroonga: public handler
int *key_min_len;
int *key_max_len;
+ longlong limit;
+ grn_table_sort_key *sort_keys;
+ int n_sort_keys;
+
bool count_skip;
+ bool fast_order_limit;
+ bool fast_order_limit_with_index;
public:
ha_mroonga(handlerton *hton, TABLE_SHARE *share);
@@ -158,6 +166,7 @@ public:
private:
void check_count_skip(key_part_map start_key_part_map,
key_part_map end_key_part_map, bool fulltext);
+ void check_fast_order_limit();
void store_fields_from_primary_table(uchar *buf, grn_id rid);
};
Added: test/sql/r/order_limit_performance.result (+126 -0) 100644
===================================================================
--- /dev/null
+++ test/sql/r/order_limit_performance.result 2010-11-23 15:14:17 +0000 (db2c01e)
@@ -0,0 +1,126 @@
+install plugin groonga soname 'ha_groonga.so';
+set storage_engine=groonga;
+drop table if exists t1;
+create table t1 (c1 int primary key, c2 int, c3 text, _id int, _score float, key idx1(c2), fulltext index ft(c3));
+insert into t1 values(1,10,"aa ii uu ee oo",null,null);
+insert into t1 values(2,20,"ka ki ku ke ko",null,null);
+insert into t1 values(3,30,"ii si ii se ii",null,null);
+insert into t1 values(4,40,"ta ti tu te to",null,null);
+insert into t1 values(5,50,"aa ii uu ii oo",null,null);
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 0
+select * from t1 where match(c3) against("ii") order by c1 desc;
+c1 c2 c3 _id _score
+5 50 aa ii uu ii oo 5 2
+3 30 ii si ii se ii 3 3
+1 10 aa ii uu ee oo 1 1
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 0
+select * from t1 where match(c3) against("ii") order by c1 desc limit 1;
+c1 c2 c3 _id _score
+5 50 aa ii uu ii oo 5 2
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 1
+select * from t1 where match(c3) against("ii") order by c1;
+c1 c2 c3 _id _score
+1 10 aa ii uu ee oo 1 1
+3 30 ii si ii se ii 3 3
+5 50 aa ii uu ii oo 5 2
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 1
+select * from t1 where match(c3) against("ii") order by c1 limit 1;
+c1 c2 c3 _id _score
+1 10 aa ii uu ee oo 1 1
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 2
+select * from t1 where match(c3) against("ii") order by c2 desc;
+c1 c2 c3 _id _score
+5 50 aa ii uu ii oo 5 2
+3 30 ii si ii se ii 3 3
+1 10 aa ii uu ee oo 1 1
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 2
+select * from t1 where match(c3) against("ii") order by c2 desc limit 1;
+c1 c2 c3 _id _score
+5 50 aa ii uu ii oo 5 2
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 3
+select * from t1 where match(c3) against("ii") order by c2;
+c1 c2 c3 _id _score
+1 10 aa ii uu ee oo 1 1
+3 30 ii si ii se ii 3 3
+5 50 aa ii uu ii oo 5 2
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 3
+select * from t1 where match(c3) against("ii") order by c2 limit 1;
+c1 c2 c3 _id _score
+1 10 aa ii uu ee oo 1 1
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 4
+select c3, _score from t1 where match(c3) against("ii") order by _score desc;
+c3 _score
+ii si ii se ii 3
+aa ii uu ii oo 2
+aa ii uu ee oo 1
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 4
+select c3, _score from t1 where match(c3) against("ii") order by _score desc limit 1, 1;
+c3 _score
+aa ii uu ii oo 2
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 5
+select c3, _score from t1 where match(c3) against("ii") order by _score;
+c3 _score
+aa ii uu ee oo 1
+aa ii uu ii oo 2
+ii si ii se ii 3
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 5
+select c3, _score from t1 where match(c3) against("ii") order by _score limit 1;
+c3 _score
+aa ii uu ee oo 1
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 6
+select c3, _id from t1 where match(c3) against("ii") order by _id desc;
+c3 _id
+aa ii uu ii oo 5
+ii si ii se ii 3
+aa ii uu ee oo 1
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 6
+select c3, _id from t1 where match(c3) against("ii") order by _id desc limit 1;
+c3 _id
+aa ii uu ii oo 5
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 7
+select c3, _id from t1 where match(c3) against("ii") order by _id;
+c3 _id
+aa ii uu ee oo 1
+ii si ii se ii 3
+aa ii uu ii oo 5
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 7
+select c3, _id from t1 where match(c3) against("ii") order by _id limit 1;
+c3 _id
+aa ii uu ee oo 1
+show status like 'groonga_fast_order_limit';
+Variable_name Value
+groonga_fast_order_limit 8
+drop table t1;
+uninstall plugin groonga;
Modified: test/sql/t/count_performance.test (+58 -58)
===================================================================
--- test/sql/t/count_performance.test 2010-11-23 14:06:40 +0000 (f6bc581)
+++ test/sql/t/count_performance.test 2010-11-23 15:14:17 +0000 (8d7fd87)
@@ -1,58 +1,58 @@
-# Copyright(C) 2010 Kentoku SHIBA
-#
-# This library is free software; you can redistribute it and/or
-# modify it under the terms of the GNU Lesser General Public
-# License as published by the Free Software Foundation; either
-# version 2.1 of the License, or (at your option) any later version.
-#
-# This library is distributed in the hope that it will be useful,
-# but WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-# Lesser General Public License for more details.
-#
-# You should have received a copy of the GNU Lesser General Public
-# License along with this library; if not, write to the Free Software
-# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
-
---source suite/groonga/include/groonga.inc
-
---disable_warnings
-drop table if exists t1, t2, t3;
---enable_warnings
-
-create table t1 (c1 int primary key, c2 int, c3 text, key idx1(c2), fulltext index ft(c3));
-insert into t1 values(1,10,"aa ii uu ee oo");
-insert into t1 values(2,20,"ka ki ku ke ko");
-insert into t1 values(3,30,"sa si su se so");
-insert into t1 values(4,40,"ta ti tu te to");
-insert into t1 values(5,50,"aa ii uu ee oo");
-show status like 'groonga_count_skip';
-select * from t1;
-show status like 'groonga_count_skip';
-select count(*) from t1;
-show status like 'groonga_count_skip';
-select * from t1 force index(primary) where c1 between 2 and 4;
-show status like 'groonga_count_skip';
-select count(*) from t1 force index(primary) where c1 between 2 and 4;
-show status like 'groonga_count_skip';
-select c1 from t1 force index(primary) where c1 < 3;
-show status like 'groonga_count_skip';
-select count(c1) from t1 force index(primary) where c1 < 3;
-show status like 'groonga_count_skip';
-select 1 from t1 force index(primary) where c1 > 3;
-show status like 'groonga_count_skip';
-select count(1) from t1 force index(primary) where c1 > 3;
-show status like 'groonga_count_skip';
-select * from t1 where match(c3) against("su");
-show status like 'groonga_count_skip';
-select count(*) from t1 where match(c3) against("su");
-show status like 'groonga_count_skip';
-select * from t1 where match(c3) against("+su" in boolean mode);
-show status like 'groonga_count_skip';
-select count(*) from t1 where match(c3) against("+su" in boolean mode);
-show status like 'groonga_count_skip';
-select * from t1 force index(idx1) where c2 between 20 and 40;
-show status like 'groonga_count_skip';
-select count(*) from t1 force index(idx1) where c2 between 20 and 40;
-show status like 'groonga_count_skip';
-drop table t1;
+# Copyright(C) 2010 Kentoku SHIBA
+#
+# This library is free software; you can redistribute it and/or
+# modify it under the terms of the GNU Lesser General Public
+# License as published by the Free Software Foundation; either
+# version 2.1 of the License, or (at your option) any later version.
+#
+# This library is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+# Lesser General Public License for more details.
+#
+# You should have received a copy of the GNU Lesser General Public
+# License along with this library; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+--source suite/groonga/include/groonga.inc
+
+--disable_warnings
+drop table if exists t1, t2, t3;
+--enable_warnings
+
+create table t1 (c1 int primary key, c2 int, c3 text, key idx1(c2), fulltext index ft(c3));
+insert into t1 values(1,10,"aa ii uu ee oo");
+insert into t1 values(2,20,"ka ki ku ke ko");
+insert into t1 values(3,30,"sa si su se so");
+insert into t1 values(4,40,"ta ti tu te to");
+insert into t1 values(5,50,"aa ii uu ee oo");
+show status like 'groonga_count_skip';
+select * from t1;
+show status like 'groonga_count_skip';
+select count(*) from t1;
+show status like 'groonga_count_skip';
+select * from t1 force index(primary) where c1 between 2 and 4;
+show status like 'groonga_count_skip';
+select count(*) from t1 force index(primary) where c1 between 2 and 4;
+show status like 'groonga_count_skip';
+select c1 from t1 force index(primary) where c1 < 3;
+show status like 'groonga_count_skip';
+select count(c1) from t1 force index(primary) where c1 < 3;
+show status like 'groonga_count_skip';
+select 1 from t1 force index(primary) where c1 > 3;
+show status like 'groonga_count_skip';
+select count(1) from t1 force index(primary) where c1 > 3;
+show status like 'groonga_count_skip';
+select * from t1 where match(c3) against("su");
+show status like 'groonga_count_skip';
+select count(*) from t1 where match(c3) against("su");
+show status like 'groonga_count_skip';
+select * from t1 where match(c3) against("+su" in boolean mode);
+show status like 'groonga_count_skip';
+select count(*) from t1 where match(c3) against("+su" in boolean mode);
+show status like 'groonga_count_skip';
+select * from t1 force index(idx1) where c2 between 20 and 40;
+show status like 'groonga_count_skip';
+select count(*) from t1 force index(idx1) where c2 between 20 and 40;
+show status like 'groonga_count_skip';
+drop table t1;
Added: test/sql/t/order_limit_performance.test (+66 -0) 100644
===================================================================
--- /dev/null
+++ test/sql/t/order_limit_performance.test 2010-11-23 15:14:17 +0000 (117275b)
@@ -0,0 +1,66 @@
+# Copyright(C) 2010 Kentoku SHIBA
+#
+# This library is free software; you can redistribute it and/or
+# modify it under the terms of the GNU Lesser General Public
+# License as published by the Free Software Foundation; either
+# version 2.1 of the License, or (at your option) any later version.
+#
+# This library is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+# Lesser General Public License for more details.
+#
+# You should have received a copy of the GNU Lesser General Public
+# License along with this library; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+
+--disable_warnings
+install plugin groonga soname 'ha_groonga.so';
+set storage_engine=groonga;
+drop table if exists t1;
+--enable_warnings
+
+create table t1 (c1 int primary key, c2 int, c3 text, _id int, _score float, key idx1(c2), fulltext index ft(c3));
+insert into t1 values(1,10,"aa ii uu ee oo",null,null);
+insert into t1 values(2,20,"ka ki ku ke ko",null,null);
+insert into t1 values(3,30,"ii si ii se ii",null,null);
+insert into t1 values(4,40,"ta ti tu te to",null,null);
+insert into t1 values(5,50,"aa ii uu ii oo",null,null);
+show status like 'groonga_fast_order_limit';
+select * from t1 where match(c3) against("ii") order by c1 desc;
+show status like 'groonga_fast_order_limit';
+select * from t1 where match(c3) against("ii") order by c1 desc limit 1;
+show status like 'groonga_fast_order_limit';
+select * from t1 where match(c3) against("ii") order by c1;
+show status like 'groonga_fast_order_limit';
+select * from t1 where match(c3) against("ii") order by c1 limit 1;
+show status like 'groonga_fast_order_limit';
+select * from t1 where match(c3) against("ii") order by c2 desc;
+show status like 'groonga_fast_order_limit';
+select * from t1 where match(c3) against("ii") order by c2 desc limit 1;
+show status like 'groonga_fast_order_limit';
+select * from t1 where match(c3) against("ii") order by c2;
+show status like 'groonga_fast_order_limit';
+select * from t1 where match(c3) against("ii") order by c2 limit 1;
+show status like 'groonga_fast_order_limit';
+select c3, _score from t1 where match(c3) against("ii") order by _score desc;
+show status like 'groonga_fast_order_limit';
+select c3, _score from t1 where match(c3) against("ii") order by _score desc limit 1, 1;
+show status like 'groonga_fast_order_limit';
+select c3, _score from t1 where match(c3) against("ii") order by _score;
+show status like 'groonga_fast_order_limit';
+select c3, _score from t1 where match(c3) against("ii") order by _score limit 1;
+show status like 'groonga_fast_order_limit';
+select c3, _id from t1 where match(c3) against("ii") order by _id desc;
+show status like 'groonga_fast_order_limit';
+select c3, _id from t1 where match(c3) against("ii") order by _id desc limit 1;
+show status like 'groonga_fast_order_limit';
+select c3, _id from t1 where match(c3) against("ii") order by _id;
+show status like 'groonga_fast_order_limit';
+select c3, _id from t1 where match(c3) against("ii") order by _id limit 1;
+show status like 'groonga_fast_order_limit';
+drop table t1;
+
+--disable_warnings
+uninstall plugin groonga;
+--enable_warnings