|
I generated a big text file - 740m size
MariaDB [test]> select uuid() from mysql.help_topic a, mysql.help_topic b, mysql.help_topic c limit 20000000 into outfile '/home/alice/t/xx3';
|
Query OK, 20000000 rows affected (6.75 sec)
|
|
MariaDB [test]> select * from bigcsv limit 10;
|
+----------+------+------+------+--------------+
|
| n1 | n2 | c1 | c2 | c3 |
|
+----------+------+------+------+--------------+
|
| 0d979bdb | 261c | 11e7 | a6a4 | 7429af1476cb |
|
| 0d979c09 | 261c | 11e7 | a6a4 | 7429af1476cb |
|
| 0d979c17 | 261c | 11e7 | a6a4 | 7429af1476cb |
|
| 0d979c25 | 261c | 11e7 | a6a4 | 7429af1476cb |
|
| 0d979c33 | 261c | 11e7 | a6a4 | 7429af1476cb |
|
| 0d979c40 | 261c | 11e7 | a6a4 | 7429af1476cb |
|
| 0d979c4e | 261c | 11e7 | a6a4 | 7429af1476cb |
|
| 0d979c5b | 261c | 11e7 | a6a4 | 7429af1476cb |
|
| 0d979c69 | 261c | 11e7 | a6a4 | 7429af1476cb |
|
| 0d979c76 | 261c | 11e7 | a6a4 | 7429af1476cb |
|
+----------+------+------+------+--------------+
|
10 rows in set (0.00 sec)
|
Then tried to create an index, the query was ok, but indeed there is no index file created on disk. But in table definition index appear. Then I tried to use it and I got an error.
MariaDB [test]> alter table bigcsv add index(n1,n2);
|
Query OK, 0 rows affected (0.00 sec)
|
Records: 0 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> show create table bigcsv;
|
| bigcsv | CREATE TABLE `bigcsv` (
|
`n1` varchar(75) NOT NULL,
|
`n2` varchar(75) NOT NULL,
|
`c1` varchar(75) DEFAULT NULL,
|
`c2` varchar(75) DEFAULT NULL,
|
`c3` varchar(75) DEFAULT NULL,
|
KEY `n1` (`n1`,`n2`)
|
) ENGINE=CONNECT DEFAULT CHARSET=latin1 `table_type`=CSV `file_name`='/home/alice/t/xx3' `sep_char`='-' |
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> explain select 1 from bigcsv where (n1,n2) = ('1','2');
|
+------+-------------+--------+------+---------------+------+---------+-------------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+------+---------------+------+---------+-------------+------+--------------------------+
|
| 1 | SIMPLE | bigcsv | ref | n1 | n1 | 154 | const,const | 10 | Using where; Using index |
|
+------+-------------+--------+------+---------------+------+---------+-------------+------+--------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select 1 from bigcsv where (n1,n2) = ('1','2');
|
ERROR 1296 (HY000): Got error 122 'Wrong index file /home/alice/t/xx3.dnx' from CONNECT
|
|
It was the same scenario with index on one field, then I tried to create the same table, but instead of defining field (n2) as varchar, it was defined as int and index was created as it expected.
[Note] may be related to https://jira.mariadb.org/browse/MDEV-11291
|