Slow InfiniDB queries, what am I doing wrong?

Question

I'm testing the InfiniDB community edition to see if it suits our needing. I imported in a single table about 10 millions rows (loading of data was surprisingly fast), and I'm trying to do some query on it, but these are the results (with NON cached queries.. if query caching exists in InfiniDB):

Query 1 (very fast):

select * from mytable limit 150000,1000
1000 rows in set (0.04 sec)

Query 2 (immediate):

select count(*) from mytable;
+----------+
| count(*) |
+----------+
|  9429378 |
+----------+
1 row in set (0.00 sec)

Ok it seems to be amazingly fast.. but:

Query 3:

 select count(title) from mytable;
 .. still going after several minutes

Query 4:

select id from mytable where id like '%ABCD%';

+------------+
| id         |
+------------+
| ABCD       |
+------------+
1 row in set (11 min 17.30 sec)

I must be doing something wrong, it's not possible that it's performing this way with so simple queries. Any Idea?

According to InfiniDB documentation, the say: "No need for indexing: Because of InfiniDB’s transparent use of both vertical and logical horizontal partitioning, there is no need for indexing." — kappa, Dec 18 '13 at 14:20
i think you will probably find that ID is not nullable, and title is, and your query is counting nulls and not nulls in order to calculate results, this kind of thing would be fixed by an index, hence the usage for em, but as infini does not use em, it is more than likely trying to find out if it is null or not, have you tried select count(title) where title is not null ???? — davethecoder, Dec 18 '13 at 14:34
I did what you said, and result is: 1 row in set (11 min 37.74 sec), I think that the point is not on null values. I thought also on an hardware limitation, but the processor stays at 10% during the whole time, disk keeps reading @120Mb/s ..and repeating 2 times the same query takes the same time. What really worries me is that huge time in punctual access to single record. — kappa, Dec 18 '13 at 15:01

score 3 · Answer 1 · answered Feb 18 '14 at 20:57

That shouldn't be the case, there does appear to be something odd going on, see quick test below.

What is your server configuration: memory/OS/CPU and platform (dedicated, virtual, cloud).
Could I get the schema declaration and method to load the data?

Which version are you using? Version 4 community has significantly more features than prior versions, i.e. core syntax matches enterprise.

Cheers, Jim T

mysql> insert into mytable select a, a from (select hex(rand() * 100000) a from lineitem limit 10000000) b;
Query OK, 10000000 rows affected (1 min 54.12 sec)
Records: 10000000  Duplicates: 0  Warnings: 0


mysql> desc mytable;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | varchar(32) | YES  |     | NULL    |       |
| title | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> select * from mytable limit 150000,1000;
+-------+-------+
| id    | title |
+-------+-------+
| E81   | E81   |
| 746A  | 746A  |
. . . 
| DFC8  | DFC8  |
| 2C56  | 2C56  |
+-------+-------+
1000 rows in set (0.07 sec)

mysql> select count(*) from mytable;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.06 sec)

mysql> select count(title) from mytable;
+--------------+
| count(title) |
+--------------+
|      10000000 |
+--------------+
1 row in set (0.09 sec)

mysql> select id from mytable where id like '%ABCD%' limit 1;
+------+
| id   |
+------+
| ABCD |
+------+
1 row in set (0.03 sec)

Slow InfiniDB queries, what am I doing wrong?

1 Answers1