CODEWITHSUNDEEP
sqlperformancetimequery-optimizationsinglestore
Vivek Aditya
Vivek Aditya

Reputation: 1173

Simple join between 3 tables takes lot of time in memsql

I ran the following query in memsql and mysql but the time taken by it is quite different.

Memsql

select count(*) from po A , cu B , tsk C  where A.customer_id = B.customer_id and B.taskid = C.id and A.domain = 5  and week(B.post_date) = 22;
+----------+
| count(*) |
+----------+
|    98952 |
+----------+
1 row in set (19.89 sec)

Mysql

select count(*) from po A , cu B , tsk C where A.customer_id = B.customer_id and B.taskid = C.id and A.domain = 5  and week(B.post_date) = 22;
+----------+
| count(*) |
+----------+
|    98952 |
+----------+
1 row in set (0.50 sec)

Why Does memsql perform so badly while mysql is so fast?

Both mysql and memsql are on the same 8GB , quad core machine. memsql has 1 master Aggregator node and 3 leaf nodes.

Does memsql perform badly if there are joins?

UPDATE

From the Doc it is clear that the table should have a shard key on columns which are expected to join on often. This allows the optimizer to minimize network traffic during the execution of the query.

So i think here i went wrong. Instead of having a shard key i had added a simple primary key on the tables.

Upvotes: 1

Views: 1187

Answers (1)

Steven
Steven

Reputation: 203

Have you tried running the query in MemSQL a second time? MemSQL compiles and caches the query execution code the first time it sees a query - MemSQL calls it code generation.

http://docs.memsql.com/latest/concepts/codegen/

When you run the query again, you should see a considerable performance speedup.

Upvotes: 2

Related Questions