Slow SELECT ... JOIN + FACET

[sanikolaev]

select ... join + facet against 2 tables of 277562 and 8671 docs is too slow:

snikolaev@dev2:~$ mysql -P9306 -h0
Server version: 6.2.13 8db8be098@24031215 dev (columnar 2.2.5 aa3504b@240304) (secondary 2.2.5 aa3504b@240304) (knn 2.2.5 aa3504b@240304) git branch master...origin/master

mysql> select title, actor_ids from movies inner join movies_liked on movies_liked.movie_id = movies.id limit 0 facet actor_ids;
Empty set (17.13 sec)
--- 0 out of 8671 results in 8s 563ms ---

+-----------+----------+
| actor_ids | count(*) |
+-----------+----------+
|      2380 |      102 |
|     10152 |        4 |
|        16 |      188 |
|     32487 |       17 |
|     19212 |        1 |
|      4081 |       23 |
|        43 |        7 |
|      1681 |        6 |
|      2338 |        9 |
|       506 |       20 |
|      3971 |       19 |
|       588 |       12 |
|      3753 |        5 |
|     19316 |        1 |
|      1207 |       35 |
|      9884 |       68 |
|      9955 |        1 |
|     36532 |        2 |
|      1302 |        1 |
|      7830 |       27 |
+-----------+----------+
20 rows in set (17.13 sec)
--- 20 out of 2267 results in 8s 563ms ---

Dataset:

snikolaev@dev2:~$ wget https://github.com/manticoresoftware/datasets/releases/download/files/movies_dummy_dataset.sql.zip

snikolaev@dev2:~$ unzip movies_dummy_dataset.sql.zip

snikolaev@dev2:~$ mysql -P9306 -h0 < movies_dummy_dataset.sql

Related issue https://github.com/manticoresoftware/manticoresearch/issues/1949 . Perhaps after fixing that, this one will become faster.

[glookka]

Related issue https://github.com/manticoresoftware/manticoresearch/issues/1949 . Perhaps after fixing that, this one will become faster.

Although I did not benchmark it, it's possible that it may have actually become slower since I disabled multi-threaded execution of such queries when I fixed #1949, see issue comments.

[sanikolaev]

Indeed it has become a little bit slower:

mysql> select title, actor_ids from movies inner join movies_liked on movies_liked.movie_id = movies.id limit 0 facet actor_ids;
Empty set (18.94 sec)
--- 0 out of 8671 results in 9s 469ms ---

+-----------+----------+
| actor_ids | count(*) |
+-----------+----------+
|      2380 |      102 |
|     10152 |        4 |
|        16 |      188 |
|     32487 |       17 |
|     19212 |        1 |
|      4081 |       23 |
|        43 |        7 |
|      1681 |        6 |
|      2338 |        9 |
|       506 |       20 |
|      3971 |       19 |
|       588 |       12 |
|      3753 |        5 |
|     19316 |        1 |
|      1207 |       35 |
|      9884 |       68 |
|      9955 |        1 |
|     36532 |        2 |
|      1302 |        1 |
|      7830 |       27 |
+-----------+----------+
20 rows in set (18.94 sec)
--- 20 out of 2267 results in 9s 469ms ---

in

mysql> show version;
+-----------+--------------------------------+
| Component | Version                        |
+-----------+--------------------------------+
| Daemon    | 6.2.13 aabec386f@24031910 dev  |

vs ~17 sec previously.

More performance issues

W/o facet it works much faster, but it's interesting that it works slower with limit 0 thatn w/o it:

mysql> select title, actor_ids from movies inner join movies_liked on movies_liked.movie_id = movies.id limit 0;
Empty set (1.44 sec)
--- 0 out of 8671 results in 1s 442ms ---

mysql> select title, actor_ids from movies inner join movies_liked on movies_liked.movie_id = movies.id limit 0;
Empty set (1.43 sec)
--- 0 out of 8671 results in 1s 435ms ---

mysql> select title, actor_ids from movies inner join movies_liked on movies_liked.movie_id = movies.id limit 0;
Empty set (1.40 sec)
--- 0 out of 8671 results in 1s 394ms ---

mysql> select title, actor_ids from movies inner join movies_liked on movies_liked.movie_id = movies.id limit 1;
+---------------------------------------------+------------------+
| title                                       | actor_ids        |
+---------------------------------------------+------------------+
| Sublime degero assumenda accendo velum pax. | 2380,10152,32487 |
+---------------------------------------------+------------------+
1 row in set (0.95 sec)
--- 1 out of >=20 results in 949ms ---

mysql> select title, actor_ids from movies inner join movies_liked on movies_liked.movie_id = movies.id limit 1;
+---------------------------------------------+------------------+
| title                                       | actor_ids        |
+---------------------------------------------+------------------+
| Sublime degero assumenda accendo velum pax. | 2380,10152,32487 |
+---------------------------------------------+------------------+
1 row in set (0.96 sec)
--- 1 out of >=20 results in 955ms ---

mysql> select title, actor_ids from movies inner join movies_liked on movies_liked.movie_id = movies.id limit 1;
+---------------------------------------------+------------------+
| title                                       | actor_ids        |
+---------------------------------------------+------------------+
| Sublime degero assumenda accendo velum pax. | 2380,10152,32487 |
+---------------------------------------------+------------------+
1 row in set (0.99 sec)
--- 1 out of >=20 results in 989ms ---