mariadbquery-optimizationsql-execution-plan

Optimize MariaDB simple query and what in fact cost is in explain json format statement


I have 2 questions:

  1. Is it any way to optimize or rephrase this query that I will be able to execute it? I was waiting for over 2 days and the query was still executing...

  2. How does the "cost" (I read that those are not units) translates to something relative that I can predict how big the query is or how long the query will be executed.

Here is my table plan:

CREATE TABLE IF NOT EXISTS alb (
  id INT NOT NULL AUTO_INCREMENT,
  iid VARCHAR(169) UNIQUE NOT NULL,
  name TEXT,
  grp VARCHAR(255),
  prod VARCHAR(255),
  barcode VARCHAR(255),
  pr_ic DECIMAL(10,2),
  pr_al DECIMAL(10,2),
  pr_ap DECIMAL(10,2),
  pr_mp DECIMAL(10,2),
  q_ic INT,
  q_al INT,
  q_ap INT,
  q_mp INT,
  u_pr DECIMAL(10,2),
  ic VARCHAR(169),
  al VARCHAR(169),
  ap VARCHAR(169),
  mp VARCHAR(169),
  td VARCHAR(169),
  td_p INT,
  INDEX( iid, ic, al, ap, mp, td ),
  PRIMARY KEY( id )
);

CREATE TABLE IF NOT EXISTS al (
  id INT NOT NULL AUTO_INCREMENT,
  calc_pr DECIMAL(10,2),
  calc_pr_in_lc DECIMAL(10,2),
  def_barcode VARCHAR(255),
  def_barcode_quantity INT,
  name TEXT,
  grp VARCHAR(255),
  name1 TEXT,
  name2 TEXT,
  pr1 DECIMAL(10,2),
  pr1_in_lc DECIMAL(10,2),
  pr2 DECIMAL(10,2),
  pr2_in_lc DECIMAL(10,2),
  pr_in_lc DECIMAL(10,2),
  product_id VARCHAR(255),
  iid VARCHAR(255),
  iid2 VARCHAR(255),
  supplier_iid VARCHAR(255),
  tax INT,
  INDEX( iid, iid2 ),
  PRIMARY KEY( id )
);

and here is execution plan:

SELECT a.iid, al.iid
  FROM alb a
 INNER JOIN al ON a.al LIKE concat( al.iid, '%' );
MariaDB [db]> EXPLAIN FORMAT=JSON SELECT a.iid, al.iid FROM alb a INNER JOIN al ON a.al LIKE concat( al.iid, '%')\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost": 301476723.4,
    "nested_loop": [
      {
        "table": {
          "table_name": "a",
          "access_type": "index",
          "key": "iid_2",
          "key_length": "3059",
          "used_key_parts": ["iid", "ic", "al", "ap", "mp", "td"],
          "loops": 1,
          "rows": 1744202,
          "cost": 627.4988767,
          "filtered": 100,
          "using_index": true
        }
      },
      {
        "block-nl-join": {
          "table": {
            "table_name": "al",
            "access_type": "index",
            "key": "iid",
            "key_length": "1536",
            "used_key_parts": ["iid", "iid2"],
            "loops": 1744202,
            "rows": 1859159,
            "cost": 301476095.9,
            "filtered": 100,
            "using_index": true
          },
          "buffer_type": "flat",
          "buffer_size": "256Kb",
          "join_type": "BNL",
          "attached_condition": "a.al like concat(al.iid,'%')"
        }
      }
    ]
  }
}
1 row in set (0.003 sec)

Sample values:

+---------------+-----------------+
| alb.al        | al.iid          |
+---------------+-----------------+
| 0 001 107 438 | 0 001 107 438 3 |
| 0 001 107 444 | 0 001 107 444   |
| 0 001 107 446 | 0 001 107 446   |
| 0 001 107 476 | 0 001 107 476   |
| 0 001 108 021 | 0 001 108 021   |
| 0 001 108 230 | 0 001 108 230   |
| 0 001 108 237 | 0 001 108 237   |
| 0 001 108 239 | 0 001 108 239   |
| 0 001 108 241 | 0 001 108 241   |
| 0 001 108 405 | 0 001 108 405   |
+---------------+-----------------+

Solution

  • alb:  INDEX(al,  iid)
    

    Would be a better "covering" index for that query, add it. This may speed up the query significantly.