Hi Sergei You can repeat this very easily: MariaDB [test]> CREATE TABLE t_connect (c INT PRIMARY KEY) ENGINE=CONNECT TABLE_TYPE=CSV; Query OK, 0 rows affected, 1 warning (0.11 sec) Warning (Code 1105): No file name. Table will use t_connect.csv MariaDB [test]> INSERT INTO t_connect VALUES (1),(2);Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test]> EXPLAIN SELECT MAX(c) FROM t_connect;+------+-------------+-----------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-----------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t_connect | ALL | NULL | NULL | NULL | NULL | 4 | | +------+-------------+-----------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) But adding more rows, the difference between the rows value and the reality becomes big. Regards Federico -------------------------------------------- Mer 26/3/14, Sergei Golubchik <serg@mariadb.org> ha scritto: Oggetto: Re: [Maria-discuss] EXPLAIN and CONNECT A: "Federico Razzoli" <federico_raz@yahoo.it> Cc: maria-discuss@lists.launchpad.net Data: Mercoledì 26 marzo 2014, 20:44 Hi, Federico! On Mar 23, Federico Razzoli wrote:
Hello list
I found out that EXPLAIN SELECT aggregate_func() FROM connect_table returns a "rows" value which is much greater than the rows in the table. For example:
MariaDB [test]> SELECT COUNT(*) FROM t; +----------+ | COUNT(*) | +----------+ | 1000000 |
+----------+
1 row in set (0.49 sec)
MariaDB [test]> EXPLAIN SELECT COUNT(*) FROM t;
+------+-------------+-------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 7783705 | |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------+
So, my question is:
is there any difference in the output of EXPLAIN
with CONNECT? Is CONNECT really reading 7.8 millions rows in a table with only 1 million rows?
No, most probably it means that CONNECT estimates in this case are way off. Could you show a complete test case for this? Regards, Sergei