maria-developers team mailing list archive

[James Briggs <james.briggs@xxxxxxxxx>]

Hi Kazuhiko Shiozaki-san.
My permute-indexes tool is handy when doingperformance tuning/optimizer analysis:
https://github.com/jamesbriggs/mysql-permute-indexes
Thanks, James Briggs.
--
Cassandra/MySQL DBA. Available in San Jose area or remote.
cass_top: https://github.com/jamesbriggs/cassandra-top

      From: Kazuhiko Shiozaki <kazuhiko@xxxxxxxxxx>
 To: maria-developers@xxxxxxxxxxxxxxxxxxx 
 Sent: Tuesday, December 2, 2014 6:22 AM
 Subject: [Maria-developers] better execution plan possible with INNER JOIN of mroonga (or whatever storage engine) ?
   
Hello !

I am investigating a slow query and have a question about execution plan
with mroonga (possibly any storage engine?).

(with MariaDB 10.0.15.)

Here are tables :

CREATE TABLE catalog (
  uid bigint(20) unsigned NOT NULL,
  path varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE catalog_full_text (
  uid bigint(20) unsigned NOT NULL,
  title varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  PRIMARY KEY (uid),
  FULLTEXT KEY title (title) COMMENT 'parser
"TokenBigramSplitSymbolAlphaDigit"'
) ENGINE=Mroonga DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE category (
  uid bigint(20) unsigned NOT NULL,
  category_uid bigint(20) unsigned NOT NULL DEFAULT '0',
  base_category_uid bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (uid,category_uid,base_category_uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Here is the query :

SELECT DISTINCT
  catalog.path,
  catalog.uid
FROM
  (
    (
      catalog AS catalog
      INNER JOIN
      (
        category AS category
        INNER JOIN
        catalog_full_text AS source_title_full_text
        ON
        category.base_category_uid = 18482
        AND source_title_full_text.uid = category.category_uid
      )
      ON
      category.uid = catalog.uid
    )
    INNER JOIN
    catalog_full_text AS title_full_text
    ON
    title_full_text.uid = catalog.uid
  )
WHERE
  MATCH (source_title_full_text.title) AGAINST ('bar' IN BOOLEAN MODE)
  AND MATCH (title_full_text.title) AGAINST ('foo' IN BOOLEAN MODE)

Here is the output of EXPLAIN :

*************************** 1. row ***************************
          id: 1
  select_type: SIMPLE
        table: source_title_full_text
        type: fulltext
possible_keys: PRIMARY,title
          key: title
      key_len: 0
          ref:
        rows: 1
        Extra: Using where with pushed condition; Using temporary
*************************** 2. row ***************************
          id: 1
  select_type: SIMPLE
        table: title_full_text
        type: fulltext
possible_keys: PRIMARY,title
          key: title
      key_len: 0
          ref:
        rows: 1
        Extra: Using where with pushed condition
*************************** 3. row ***************************
          id: 1
  select_type: SIMPLE
        table: catalog
        type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: test.title_full_text.uid
        rows: 1
        Extra:
*************************** 4. row ***************************
          id: 1
  select_type: SIMPLE
        table: category
        type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 24
          ref:
test.title_full_text.uid,test.source_title_full_text.uid,const
        rows: 1
        Extra: Using index; Distinct

As you see, we have two full-text queries at the beginning and both have
'rows = 1'.

In a slow query case, 25k records mathces with the first full-text query
and 1.6k records matches with the second full-text query.

When I manually modify the order of INNER JOIN so that the 'lighter'
full-text query appears first, EXPLAIN output is different as well and
the performance is much improved.

Accoding to Kohei, a Mroonga developer, we always have 'rows = 1' for
mroonga full-text query because MariaDB does not request such value to
mroonga (JOIN::get_examined_rows() in sql/sql_select.cc), and it is
possible to modify mroonga to return an estimation of rows if MariaDB
has such an API.

So here are my questions :

* can MariaDB request an 'estimation of rows' to mroonga (or whatever
storage engine) ?
* can such information help to decide a better execution plan with INNER
JOIN like above ?

Thanks in advance !
-- 
Kazuhiko Shiozaki, Nexedi SA Senior Consultant
Nexedi: Consulting and Development of Free / Open Source Software
http://www.nexedi.com/
ERP5: Full Featured High End Open Source ERP
http://www.erp5.com/

_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to    : maria-developers@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-developers
More help  : https://help.launchpad.net/ListHelp