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 <s...@mariadb.org> ha scritto:
Oggetto: Re: [Maria-discuss] EXPLAIN and CONNECT
A: "Federico Razzoli" <federico_...@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
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
