maria-discuss team mailing list archive

[Federico Razzoli <federico_raz@xxxxxxxx>]

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@xxxxxxxxxxx> ha scritto:

 Oggetto: Re: [Maria-discuss] EXPLAIN and CONNECT
 A: "Federico Razzoli" <federico_raz@xxxxxxxx>
 Cc: maria-discuss@xxxxxxxxxxxxxxxxxxx
 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