Details
Minor
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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)
Is this with the very same data, that you are able to upload? If so then yes, please do upload it.
Uploaded as MDEV-4642_test.sql.bz2 in private folder.
Thank you
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?
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).
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.
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.
Thank you, the workaround is ok.
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.