[MDEV-4642] Index ignored on a query based on Aria table, while works fine with MyISAM Created: 2013-06-12  Updated: 2022-02-21

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - Aria
Affects Version/s: 10.0.3, 5.5.31, 5.5
Fix Version/s: 5.5

Type: Bug Priority: Minor
Reporter: Risato Stefano Assignee: Michael Widenius
Resolution: Unresolved Votes: 0
Labels: not-10.1, not-10.2, not-10.3, not-10.4
Environment:

Both Windows and Linux

Attachments: Text File my.cnf     File my.ini    

 Description   

In a query I have a table with 7 million records, joined with another table with 300.000 records.

First table:

CREATE TABLE `data` (
 
	`Unita` CHAR(8) NOT NULL DEFAULT '',
 
	`Fase` CHAR(20) NOT NULL,
 
	`TipoU` CHAR(2) NOT NULL,
 
	`ID` CHAR(15) NOT NULL DEFAULT '',
 
	`FSTD` FLOAT NOT NULL DEFAULT '0',
 
	INDEX `Main2` (`Unita`, `TipoU`, `Fase`)
 
)

Second table:

CREATE TABLE `aggrs` (
 
	`Fase` CHAR(20) NOT NULL,
 
	`Processo` CHAR(6) NOT NULL,
 
	`TipoU` CHAR(2) NOT NULL,
 
	`Unita` CHAR(8) NOT NULL,
 
	`Liv` CHAR(2) NOT NULL,
 
	`Ragr1` CHAR(8) NULL DEFAULT NULL,
 
	`Ragr1Descr` CHAR(25) NULL DEFAULT NULL,
 
	`Ragr1Nome` CHAR(25) NULL DEFAULT NULL,
 
	`Ragr2` CHAR(8) NULL DEFAULT NULL,
 
	`Ragr2Descr` CHAR(25) NULL DEFAULT NULL,
 
	`Ragr2Nome` CHAR(25) NULL DEFAULT NULL,
 
	PRIMARY KEY (`Fase`, `Processo`, `TipoU`, `Unita`, `Liv`),
 
	INDEX `Ragr2` (`Ragr2`)
 
)

In the following query

SELECT STRAIGHT_JOIN SUM(FSTD)
 
FROM aggrs ag USE INDEX (Ragr2)
 
JOIN data pr USE INDEX (Main2) ON (ag.Unita = pr.Unita) AND (ag.TipoU = pr.TipoU) AND (ag.Fase = pr.Fase)
 
WHERE ag.Ragr2='DD4M';

the index Main2 is ignored when I use Aria engine, work fine with MyISAM. The explain plan show less than 1000 rows to read with MyISAM, and the full 7 million rows with Aria.

If you need I can upload on your ftp the dump file to recreate the test case (50MB bz2).

Thank you.

 Comments   
Comment by Elena Stepanova [ 2013-06-12 ]

Hi,

Could you please run SHOW INDEX IN `data` and SHOW INDEX IN `aggrs` both when your index is used (with MyISAM), and when it's ignored (with Aria), and paste the results?

Did you try to run ANALYZE TABLE?

Thanks.

Comment by Risato Stefano [ 2013-06-12 ]

Yes, I already ran ANALYZE TABLE; Here the results of the show index:

mysql> show index in data_myisam;
--------------------------------------------------------------------------------------------------------------------------

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment

--------------------------------------------------------------------------------------------------------------------------

data_myisam 1 Main2 1 Unita A 1595 NULL NULL   BTREE    
data_myisam 1 Main2 2 TipoU A 1859 NULL NULL   BTREE    
data_myisam 1 Main2 3 Fase A 11563 NULL NULL   BTREE    

--------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.01 sec)

mysql> show index in data_aria;
------------------------------------------------------------------------------------------------------------------------

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment

------------------------------------------------------------------------------------------------------------------------

data_aria 1 Main2 1 Unita A 1 NULL NULL   BTREE    
data_aria 1 Main2 2 TipoU A 1 NULL NULL   BTREE    
data_aria 1 Main2 3 Fase A 1 NULL NULL   BTREE    

------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.01 sec)

mysql> analyze table data_aria;
----------------------------------------------------------+

Table Op Msg_type Msg_text

----------------------------------------------------------+

test.data_aria analyze status Table is already up to date

----------------------------------------------------------+
1 row in set (0.01 sec)

Comment by Elena Stepanova [ 2013-06-12 ]

Is this with the very same data, that you are able to upload? If so then yes, please do upload it.

Comment by Risato Stefano [ 2013-06-12 ]

Uploaded as MDEV-4642_test.sql.bz2 in private folder.
Thank you

Comment by Elena Stepanova [ 2013-06-13 ]

Hi, I loaded your data and I'm getting identical plans with the MyISAM table and Aria table:

MariaDB [test]> EXPLAIN EXTENDED SELECT STRAIGHT_JOIN SUM(FSTD) FROM aggrs ag USE INDEX (Ragr2) JOIN data_aria pr USE INDEX (Main2) ON (ag.Unita = pr.Unita) AND (ag.TipoU = pr.TipoU) AND (ag.Fase = pr.Fase) WHERE ag.Ragr2='DD4M';
----------------------------------------------------------------------------------------------------------------------------

id select_type table type possible_keys key key_len ref rows filtered Extra

----------------------------------------------------------------------------------------------------------------------------

1 SIMPLE ag ref Ragr2 Ragr2 9 const 752 100.00 Using index condition
1 SIMPLE pr ref Main2 Main2 30 test.ag.Unita,test.ag.TipoU,test.ag.Fase 668 100.00  

----------------------------------------------------------------------------------------------------------------------------
2 rows in set, 1 warning (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED SELECT STRAIGHT_JOIN SUM(FSTD) FROM aggrs ag USE INDEX (Ragr2) JOIN data_myisam pr USE INDEX (Main2) ON (ag.Unita = pr.Unita) AND (ag.TipoU = pr.TipoU) AND (ag.Fase = pr.Fase) WHERE ag.Ragr2='DD4M';
----------------------------------------------------------------------------------------------------------------------------

id select_type table type possible_keys key key_len ref rows filtered Extra

----------------------------------------------------------------------------------------------------------------------------

1 SIMPLE ag ref Ragr2 Ragr2 9 const 752 100.00 Using index condition
1 SIMPLE pr ref Main2 Main2 30 test.ag.Unita,test.ag.TipoU,test.ag.Fase 668 100.00  

----------------------------------------------------------------------------------------------------------------------------
2 rows in set, 1 warning (0.00 sec)

I'm using the top of maria/5.5 tree, but I'll try the release binaries as well. Could you please send your cnf file as well?

Comment by Risato Stefano [ 2013-06-13 ]

Hello, here attached the my.ini used in 5.5.31 Windows 32 bit (production) and my.cnf used in 10.0.0.3 Linux 64 bit (test).

Comment by Elena Stepanova [ 2013-06-14 ]

Thank you, the config file helped.
The culprit is aria_repair_threads. It affects the index creation in a bad way (we see that it gets cardinality 1, which is obviously wrong).
If you need to work around the problem, you can set aria_repair_threads=1 in your session and, for example, run
alter table data_aria engine=aria.

Comment by Elena Stepanova [ 2013-06-14 ]

So, the problem appears due to aria_repair_threads > 1. To reproduce, use the SQL file from FTP. It takes quite a while to load it. I tried to reduce the data, but there is only that much one can do, ~ 4 mln rows are required for the problem to show up. However, the table data_myisam is not needed for the test, so it can be skipped (removed from the dump before loading). Basically, the first 480 lines of the dump is enough.

I use ALTER to make the problem obvious because it's faster than reloading the data.

  • start the server with default parameters;
  • load the data;
  • run
    show index in data_aria
  • see that the cardinality is okay;
  • run
    EXPLAIN EXTENDED SELECT STRAIGHT_JOIN SUM(FSTD) FROM aggrs ag USE INDEX (Ragr2) JOIN data_aria pr USE INDEX (Main2) ON (ag.Unita = pr.Unita) AND (ag.TipoU = pr.TipoU) AND (ag.Fase = pr.Fase) WHERE ag.Ragr2='DD4M';
  • see that the plan uses Main2 index;
  • execute
    SET aria_repair_threads=3;
    ALTER TABLE data_aria ENGINE=aria;
  • run show index and see that the cardinality has become 1;
  • run the explain and see that it doesn't use Main2 index anymore;
  • execute
    SET aria_repair_threads=1;
    ALTER TABLE data_aria ENGINE=aria;
  • run show index and explain and see that things are back to normal.

Output from the test described above:

MariaDB [test]> show index in data_aria;
------------------------------------------------------------------------------------------------------------------------

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment

------------------------------------------------------------------------------------------------------------------------

data_aria 1 Main2 1 Unita A 1595 NULL NULL   BTREE    
data_aria 1 Main2 2 TipoU A 1859 NULL NULL   BTREE    
data_aria 1 Main2 3 Fase A 11563 NULL NULL   BTREE    

------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED SELECT STRAIGHT_JOIN SUM(FSTD) FROM aggrs ag USE INDEX (Ragr2) JOIN data_aria pr USE INDEX (Main2) ON (ag.Unita = pr.Unita) AND (ag.TipoU = pr.TipoU) AND (ag.Fase = pr.Fase) WHERE ag.Ragr2='DD4M';
----------------------------------------------------------------------------------------------------------------------------

id select_type table type possible_keys key key_len ref rows filtered Extra

----------------------------------------------------------------------------------------------------------------------------

1 SIMPLE ag ref Ragr2 Ragr2 9 const 752 100.00 Using index condition
1 SIMPLE pr ref Main2 Main2 30 test.ag.Unita,test.ag.TipoU,test.ag.Fase 668 100.00  

----------------------------------------------------------------------------------------------------------------------------
2 rows in set, 1 warning (0.01 sec)

MariaDB [test]> set aria_repair_threads=3;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> alter table data_aria engine=aria;
Query OK, 7724250 rows affected (3 min 51.78 sec)
Records: 7724250 Duplicates: 0 Warnings: 0

MariaDB [test]> show index in data_aria;
------------------------------------------------------------------------------------------------------------------------

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment

------------------------------------------------------------------------------------------------------------------------

data_aria 1 Main2 1 Unita A 1 NULL NULL   BTREE    
data_aria 1 Main2 2 TipoU A 1 NULL NULL   BTREE    
data_aria 1 Main2 3 Fase A 1 NULL NULL   BTREE    

------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.01 sec)

MariaDB [test]> EXPLAIN EXTENDED SELECT STRAIGHT_JOIN SUM(FSTD) FROM aggrs ag USE INDEX (Ragr2) JOIN data_aria pr USE INDEX (Main2) ON (ag.Unita = pr.Unita) AND (ag.TipoU = pr.TipoU) AND (ag.Fase = pr.Fase) WHERE ag.Ragr2='DD4M';
----------------------------------------------------------------------------------------------------------------------

id select_type table type possible_keys key key_len ref rows filtered Extra

----------------------------------------------------------------------------------------------------------------------

1 SIMPLE ag ref Ragr2 Ragr2 9 const 752 100.00 Using index condition
1 SIMPLE pr ALL Main2 NULL NULL NULL 7724250 75.00 Using where; Using join buffer (flat, BNL join)

----------------------------------------------------------------------------------------------------------------------
2 rows in set, 1 warning (0.00 sec)

MariaDB [test]> set aria_repair_threads=1;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> alter table data_aria engine=aria;
Query OK, 7724250 rows affected (3 min 59.84 sec)
Records: 7724250 Duplicates: 0 Warnings: 0

MariaDB [test]> show index in data_aria;
------------------------------------------------------------------------------------------------------------------------

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment

------------------------------------------------------------------------------------------------------------------------

data_aria 1 Main2 1 Unita A 1595 NULL NULL   BTREE    
data_aria 1 Main2 2 TipoU A 1859 NULL NULL   BTREE    
data_aria 1 Main2 3 Fase A 11563 NULL NULL   BTREE    

------------------------------------------------------------------------------------------------------------------------
3 rows in set (0.01 sec)

MariaDB [test]> EXPLAIN EXTENDED SELECT STRAIGHT_JOIN SUM(FSTD) FROM aggrs ag USE INDEX (Ragr2) JOIN data_aria pr USE INDEX (Main2) ON (ag.Unita = pr.Unita) AND (ag.TipoU = pr.TipoU) AND (ag.Fase = pr.Fase) WHERE ag.Ragr2='DD4M';
----------------------------------------------------------------------------------------------------------------------------

id select_type table type possible_keys key key_len ref rows filtered Extra

----------------------------------------------------------------------------------------------------------------------------

1 SIMPLE ag ref Ragr2 Ragr2 9 const 752 100.00 Using index condition
1 SIMPLE pr ref Main2 Main2 30 test.ag.Unita,test.ag.TipoU,test.ag.Fase 668 100.00  

----------------------------------------------------------------------------------------------------------------------------
2 rows in set, 1 warning (0.00 sec)

I suppose the problem exists in previous versions as well, but it's probably not worth fixing it there, so I didn't try to reproduce.

Comment by Risato Stefano [ 2013-06-14 ]

Thank you, the workaround is ok.

Comment by Elena Stepanova [ 2019-05-02 ]

Still reproducible on current 5.5 (71a748d5), couldn't reproduce on 10.x.

Generated at Thu Feb 08 06:57:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.