# Query Tuning

**URL:** https://www.singlestore.com/forum/c/support/query-tuning/13.md

[Latest](https://www.singlestore.com/forum/latest.md) · [Categories](https://www.singlestore.com/forum/categories.md) · [Tags](https://www.singlestore.com/forum/tags.md)

---

## [Increase in query time after I created a sharded key on one of the join columns](https://www.singlestore.com/forum/t/increase-in-query-time-after-i-created-a-sharded-key-on-one-of-the-join-columns/8363)

<div class="topic-metadata">

**Author:** [@shanelobo18](https://www.singlestore.com/forum/u/shanelobo18)\
**Replies:** 4\
**Last updated:** [September 15, 2025, 5:57pm UTC](https://www.singlestore.com/forum/t/increase-in-query-time-after-i-created-a-sharded-key-on-one-of-the-join-columns/8363 "2025-09-15T17:57:10Z")

</div>

Hi team, So I was using sample data from the employees table (from the s3 bucket singlestore-education/datasets/db\_employees/) and was tuning the below query SELECT e.first\_name, e.last\_name, d.dept\_name, t.title, t.fr…

---

## [Leaf Error 1128 Attempt to bind function failed because binding is already complete](https://www.singlestore.com/forum/t/leaf-error-1128-attempt-to-bind-function-failed-because-binding-is-already-complete/7985)

<div class="topic-metadata">

**Author:** [@kitaevsd](https://www.singlestore.com/forum/u/kitaevsd)\
**Replies:** 1\
**Last updated:** [November 5, 2024, 12:22am UTC](https://www.singlestore.com/forum/t/leaf-error-1128-attempt-to-bind-function-failed-because-binding-is-already-complete/7985 "2024-11-05T00:22:24Z")

</div>

Hi, could anybody helps with such type of warning: “Leaf Error 1128 Attempt to bind function failed because binding is already complete”. This warning occurs randomly and sending from each partition of leaf host (127.0…

---

## [How to do upsert without causing too much memory spike?](https://www.singlestore.com/forum/t/how-to-do-upsert-without-causing-too-much-memory-spike/7847)

<div class="topic-metadata">

**Author:** [@bhaskar](https://www.singlestore.com/forum/u/bhaskar)\
**Replies:** 4\
**Last updated:** [November 4, 2024, 3:18pm UTC](https://www.singlestore.com/forum/t/how-to-do-upsert-without-causing-too-much-memory-spike/7847 "2024-11-04T15:18:48Z")

</div>

Hi, We have to do around 100k records upsert into a table. The table has around 200-300 columns. What we are currently doing is dividing that 100k records into 1k batch and doing the upserts. But this is causing a high …

---

## [Unpivot in single not with union](https://www.singlestore.com/forum/t/unpivot-in-single-not-with-union/4682)

<div class="topic-metadata">

**Author:** [@lior.ha](https://www.singlestore.com/forum/u/lior.ha)\
**Replies:** 8\
**Last updated:** [September 23, 2024, 5:32pm UTC](https://www.singlestore.com/forum/t/unpivot-in-single-not-with-union/4682 "2024-09-23T17:32:50Z")

</div>

Hi, is there a way to do unpivot in single? and not to use union

---

## [Unsupported nested scalar subselects](https://www.singlestore.com/forum/t/unsupported-nested-scalar-subselects/1514)

<div class="topic-metadata">

**Author:** [@hitesh.upreti](https://www.singlestore.com/forum/u/hitesh.upreti)\
**Replies:** 11\
**Last updated:** [June 26, 2024, 10:28pm UTC](https://www.singlestore.com/forum/t/unsupported-nested-scalar-subselects/1514 "2024-06-26T22:28:13Z")

</div>

Getting error: MemSQL does not support this type of query: unsupported nested scalar subselects, while executing below query. delete from XYZ where processing\_dt = ((select distinct processing\_dt from XYZ where proce…

---

## [Connection reset when using DOT\_PRODUCT](https://www.singlestore.com/forum/t/connection-reset-when-using-dot-product/6755)

<div class="topic-metadata">

**Author:** [@heychazza](https://www.singlestore.com/forum/u/heychazza)\
**Replies:** 10\
**Last updated:** [April 25, 2024, 12:59am UTC](https://www.singlestore.com/forum/t/connection-reset-when-using-dot-product/6755 "2024-04-25T00:59:15Z")

</div>

Hey team, I’m currently tinkering with the vector side of it (awesome, by the way!) and having an issue. Take my query: SELECT \`title\`, \`path\`, DOT\_PRODUCT(embeddings, JSON\_ARRAY\_PACK('\[-0.012726239,-0.018567327,0.004…

---

## [Slow Query with a Costly ShuffleGroupBy Operation](https://www.singlestore.com/forum/t/slow-query-with-a-costly-shufflegroupby-operation/7391)

<div class="topic-metadata">

**Author:** [@hzheng](https://www.singlestore.com/forum/u/hzheng)\
**Replies:** 2\
**Last updated:** [March 27, 2024, 6:44am UTC](https://www.singlestore.com/forum/t/slow-query-with-a-costly-shufflegroupby-operation/7391 "2024-03-27T06:44:27Z")

</div>

I analyzed a slow query created from the B/E app and found a costly ShuffleGroupBy operation in its query’s profile. Then I try reduce the ShuffleGroupBy time by adding the WITH(LEAF\_PUSHDOWN=TRUE) hint, however it does…

---

## [Index for simple arrays (int or string) or JsonArrays?](https://www.singlestore.com/forum/t/index-for-simple-arrays-int-or-string-or-jsonarrays/7235)

<div class="topic-metadata">

**Author:** [@losch](https://www.singlestore.com/forum/u/losch)\
**Replies:** 9\
**Last updated:** [January 30, 2024, 1:02am UTC](https://www.singlestore.com/forum/t/index-for-simple-arrays-int-or-string-or-jsonarrays/7235 "2024-01-30T01:02:11Z")

</div>

Do I understand correctly that Singlestore does not currently support any kind of index for a simple array (int or string) or JsonArray? We often have data like tags = \[“home”, “office”, “worldwide”\] tag\_ids = \[3839,3…

---

## [Materialized views in the future?](https://www.singlestore.com/forum/t/materialized-views-in-the-future/2033)

<div class="topic-metadata">

**Author:** [@christoph](https://www.singlestore.com/forum/u/christoph)\
**Replies:** 14\
**Last updated:** [December 7, 2023, 10:31pm UTC](https://www.singlestore.com/forum/t/materialized-views-in-the-future/2033 "2023-12-07T22:31:10Z")

</div>

Hi everyone, we are currently working on a BI use case in which we will have near real time requirements. Essentially we will get the main entities (e.g. employee) pushed into MemSQL real time and then need to do analy…

---

## [Order by rand() does not consider LIMIT](https://www.singlestore.com/forum/t/order-by-rand-does-not-consider-limit/7260)

<div class="topic-metadata">

**Author:** [@losch](https://www.singlestore.com/forum/u/losch)\
**Replies:** 7\
**Last updated:** [October 31, 2023, 3:24am UTC](https://www.singlestore.com/forum/t/order-by-rand-does-not-consider-limit/7260 "2023-10-31T03:24:38Z")

</div>

Hi, my Singlestore version: 8.1.26 under Ubuntu 22.04.3 LTS. I’m a bit confused: on a simple query of my Articles table (with 275K entries) with: SELECT id,title FROM articles ORDER BY RAND() LIMIT 4 I rarely really …

---

## [Order of rows when using table and json\_to\_array](https://www.singlestore.com/forum/t/order-of-rows-when-using-table-and-json-to-array/6229)

<div class="topic-metadata">

**Author:** [@ahkjeldsen](https://www.singlestore.com/forum/u/ahkjeldsen)\
**Replies:** 7\
**Last updated:** [September 25, 2023, 6:57pm UTC](https://www.singlestore.com/forum/t/order-of-rows-when-using-table-and-json-to-array/6229 "2023-09-25T18:57:20Z")

</div>

Hi there. I stumbled upon something that is behaving differently than what I would expect. I have a column containing a json array, which I at times need to join with other data. I need to be able to get the index (or …

---

## [Killing queries on a single database](https://www.singlestore.com/forum/t/killing-queries-on-a-single-database/7107)

<div class="topic-metadata">

**Author:** [@attila.dobos](https://www.singlestore.com/forum/u/attila.dobos)\
**Replies:** 2\
**Last updated:** [August 28, 2023, 6:37am UTC](https://www.singlestore.com/forum/t/killing-queries-on-a-single-database/7107 "2023-08-28T06:37:22Z")

</div>

Hi there! I would like to construct a SQL query which would kill all active queries on a specific database. SELECT ID, NODE\_ID FROM information\_schema.mv\_processlist WHERE DB = ‘test’ AND STATE = ‘executing’; This q…

---

## [Updates with and without Key Columns](https://www.singlestore.com/forum/t/updates-with-and-without-key-columns/7109)

<div class="topic-metadata">

**Author:** [@Sourav.Kumar.Singh](https://www.singlestore.com/forum/u/Sourav.Kumar.Singh)\
**Replies:** 1\
**Last updated:** [August 25, 2023, 7:05pm UTC](https://www.singlestore.com/forum/t/updates-with-and-without-key-columns/7109 "2023-08-25T19:05:55Z")

</div>

We are trying to investigate a scenario where we are updating a singlestore table using the ON DUPLICATE KEY UPDATE clause and the VALUES() function. The following two cases are such that one of the INSERT query is usin…

---

## ["Multiple statements detected in a single query" while executing multiple query in one go!](https://www.singlestore.com/forum/t/multiple-statements-detected-in-a-single-query-while-executing-multiple-query-in-one-go/7082)

<div class="topic-metadata">

**Author:** [@dabhibhaskar2](https://www.singlestore.com/forum/u/dabhibhaskar2)\
**Replies:** 2\
**Last updated:** [August 18, 2023, 7:11pm UTC](https://www.singlestore.com/forum/t/multiple-statements-detected-in-a-single-query-while-executing-multiple-query-in-one-go/7082 "2023-08-18T19:11:58Z")

</div>

Hi, I am trying to run multiple queries in one go like following: “ALTER TABLE table1 ADD column column1 LONGTEXT;ALTER TABLE table1 ADD column column2 DATETIME;ALTER TABLE table1 ADD column column3 DATE;ALTER TABLE ta…

---

## [Optimize SCALAR\_VECTOR\_MAX UDF to Smooth Precipitation Data Vectors](https://www.singlestore.com/forum/t/optimize-scalar-vector-max-udf-to-smooth-precipitation-data-vectors/6933)

<div class="topic-metadata">

**Author:** [@michael.arthur](https://www.singlestore.com/forum/u/michael.arthur)\
**Replies:** 5\
**Last updated:** [August 7, 2023, 9:18pm UTC](https://www.singlestore.com/forum/t/optimize-scalar-vector-max-udf-to-smooth-precipitation-data-vectors/6933 "2023-08-07T21:18:05Z")

</div>

I have vectors of precipitation data. One year of data per hour in mm of rainfall during the hour stored as vectors of doubles. In other words, vector length = 365 \* 24. For various faults in the data collection pr…

---

## [Cross database joins](https://www.singlestore.com/forum/t/cross-database-joins/2999)

<div class="topic-metadata">

**Author:** [@smaxwell](https://www.singlestore.com/forum/u/smaxwell)\
**Replies:** 2\
**Last updated:** [July 24, 2023, 7:20pm UTC](https://www.singlestore.com/forum/t/cross-database-joins/2999 "2023-07-24T19:20:07Z")

</div>

Using SingleStore 7.3.7, I was benchmarking performance, and I joined two tables in different databases. The performance was great, considering that one of the tables had over 2 billion records. I tried moving the smal…

---

## [Select into GCS Performance](https://www.singlestore.com/forum/t/select-into-gcs-performance/7003)

<div class="topic-metadata">

**Author:** [@edson.moretti](https://www.singlestore.com/forum/u/edson.moretti)\
**Replies:** 0\
**Last updated:** [July 24, 2023, 10:40am UTC](https://www.singlestore.com/forum/t/select-into-gcs-performance/7003 "2023-07-24T10:40:43Z")

</div>

Hello, I’m exporting data from single store to GCS using the select into GCS functionality however I’m not too satisfied with the performance and I’m looking for ways to improve it. I’m using c# .net with the single st…

---

## [Should I use an UUID, Nanoid or ULID for PK?](https://www.singlestore.com/forum/t/should-i-use-an-uuid-nanoid-or-ulid-for-pk/6788)

<div class="topic-metadata">

**Author:** [@stefandevo](https://www.singlestore.com/forum/u/stefandevo)\
**Replies:** 1\
**Last updated:** [May 17, 2023, 12:15am UTC](https://www.singlestore.com/forum/t/should-i-use-an-uuid-nanoid-or-ulid-for-pk/6788 "2023-05-17T00:15:16Z")

</div>

What would be the best PK, knowing that the ID will be created “offline” (aka in code C#). UUID: VARCHAR(36), complete random Nanoid: VARCHAR(21), complete random ULID: VARCHAR(26), sortable random More related of ha…

---

## [Dot product optimisation for similarity search](https://www.singlestore.com/forum/t/dot-product-optimisation-for-similarity-search/6722)

<div class="topic-metadata">

**Author:** [@majed.bader](https://www.singlestore.com/forum/u/majed.bader)\
**Replies:** 5\
**Last updated:** [May 5, 2023, 6:25pm UTC](https://www.singlestore.com/forum/t/dot-product-optimisation-for-similarity-search/6722 "2023-05-05T18:25:22Z")

</div>

Hey :wave: I have a large table of vectors, stored as JSON in column-store. I need to perform a dot product between a single vector, and a subset of over 10k other vectors from this table (all vectors are normalised and…

---

## [Please suggest best approach to improve Join between three large tables with different shard keys](https://www.singlestore.com/forum/t/please-suggest-best-approach-to-improve-join-between-three-large-tables-with-different-shard-keys/6673)

<div class="topic-metadata">

**Author:** [@nandaswayam](https://www.singlestore.com/forum/u/nandaswayam)\
**Replies:** 1\
**Last updated:** [April 10, 2023, 6:05pm UTC](https://www.singlestore.com/forum/t/please-suggest-best-approach-to-improve-join-between-three-large-tables-with-different-shard-keys/6673 "2023-04-10T18:05:17Z")

</div>

Hi Let me give a brief idea about what we are trying to achieve. Basically we have three tables Table1 which has shard key on column linkID. It has 2 other columns postId and personId among many others. Table2 which …

---

## [IN clauses with multiple columns?](https://www.singlestore.com/forum/t/in-clauses-with-multiple-columns/5741)

<div class="topic-metadata">

**Author:** [@erica.anderson](https://www.singlestore.com/forum/u/erica.anderson)\
**Replies:** 5\
**Last updated:** [April 10, 2023, 5:39pm UTC](https://www.singlestore.com/forum/t/in-clauses-with-multiple-columns/5741 "2023-04-10T17:39:30Z")

</div>

Is there any way to produce an IN clause with multiple columns? Say I have a table with a composite primary key, and I want to batch deletes with a list of PK values. In Mysql I can do this: delete from my\_db.my\_table …

---

## [Is there any way to show a live query plan during the execution of a query?](https://www.singlestore.com/forum/t/is-there-any-way-to-show-a-live-query-plan-during-the-execution-of-a-query/6425)

<div class="topic-metadata">

**Author:** [@alau](https://www.singlestore.com/forum/u/alau)\
**Replies:** 5\
**Last updated:** [March 1, 2023, 5:37pm UTC](https://www.singlestore.com/forum/t/is-there-any-way-to-show-a-live-query-plan-during-the-execution-of-a-query/6425 "2023-03-01T17:37:41Z")

</div>

Is there any way to show a live query plan during the execution of a query?

---

## [The operating system failed to allocate memory](https://www.singlestore.com/forum/t/the-operating-system-failed-to-allocate-memory/565)

<div class="topic-metadata">

**Author:** [@arun.pradeep](https://www.singlestore.com/forum/u/arun.pradeep)\
**Replies:** 2\
**Last updated:** [January 23, 2023, 9:51am UTC](https://www.singlestore.com/forum/t/the-operating-system-failed-to-allocate-memory/565 "2023-01-23T09:51:33Z")

</div>

Hi, When I tried to execute join clause on a two tables which holds billion records, received “Memory allocation issue”. QUERY: SELECT COUNT(1) FROM tableA a JOIN tableB b ON a.id = b.id WHERE a.category = ‘S…

---

## [Getting value without quotes from json array turned into a table](https://www.singlestore.com/forum/t/getting-value-without-quotes-from-json-array-turned-into-a-table/6230)

<div class="topic-metadata">

**Author:** [@ahkjeldsen](https://www.singlestore.com/forum/u/ahkjeldsen)\
**Replies:** 2\
**Last updated:** [November 17, 2022, 7:47am UTC](https://www.singlestore.com/forum/t/getting-value-without-quotes-from-json-array-turned-into-a-table/6230 "2022-11-17T07:47:37Z")

</div>

Hi there I sometimes need to iterate over a json array and do so by using table(json\_to\_array(...)). However, when I want to use the value, it is often wrapped in quotes, due to the value being a string. When I wish to …

---

## [How to insert data from one table to another table without any duplicates](https://www.singlestore.com/forum/t/how-to-insert-data-from-one-table-to-another-table-without-any-duplicates/6099)

<div class="topic-metadata">

**Author:** [@t.sukanyareddy](https://www.singlestore.com/forum/u/t.sukanyareddy)\
**Replies:** 2\
**Last updated:** [November 9, 2022, 6:26pm UTC](https://www.singlestore.com/forum/t/how-to-insert-data-from-one-table-to-another-table-without-any-duplicates/6099 "2022-11-09T18:26:58Z")

</div>

Hi team, We are in processing for copying data from one to another table but it should be only distinct ones. so we are trying execute below syntax by stopping pipeline. INSERT INTO newtable (select distinct(\*) from ex…

---

## [Query elapsed time](https://www.singlestore.com/forum/t/query-elapsed-time/5695)

<div class="topic-metadata">

**Author:** [@nicholashjferreira](https://www.singlestore.com/forum/u/nicholashjferreira)\
**Replies:** 0\
**Last updated:** [September 30, 2022, 1:08am UTC](https://www.singlestore.com/forum/t/query-elapsed-time/5695 "2022-09-30T01:08:54Z")

</div>

Hello SingleStore, I’m currently executing some queries on SingleStore Cloud to gather metrics of elapsed runtime of these queries. But always on the first run of the query, the elapsed time is at least 10x bigger than …

---

## [When to use alter table vs create and copy for migration changes to tables?](https://www.singlestore.com/forum/t/when-to-use-alter-table-vs-create-and-copy-for-migration-changes-to-tables/5297)

<div class="topic-metadata">

**Author:** [@MGRAHAM](https://www.singlestore.com/forum/u/MGRAHAM)\
**Replies:** 3\
**Last updated:** [September 19, 2022, 10:33pm UTC](https://www.singlestore.com/forum/t/when-to-use-alter-table-vs-create-and-copy-for-migration-changes-to-tables/5297 "2022-09-19T22:33:23Z")

</div>

I know that singlestore supports online alters of tables which is a nice feature. But it is my understanding that with columnstore tables, repetitive physical alters to a table structure can cause performance problems in…

---

## [Optimizing query with where and order by desc](https://www.singlestore.com/forum/t/optimizing-query-with-where-and-order-by-desc/4994)

<div class="topic-metadata">

**Author:** [@miguilim](https://www.singlestore.com/forum/u/miguilim)\
**Replies:** 12\
**Last updated:** [September 12, 2022, 5:34am UTC](https://www.singlestore.com/forum/t/optimizing-query-with-where-and-order-by-desc/4994 "2022-09-12T05:34:10Z")

</div>

I’m currently performing a query in a table of 10M rows. Even with the sort key and indexes, the following query still scans half of the table to find the result: select \`created\_at\` from \`completed\_tasks\` where (\`user\_…

---

## [Getting lock timeout exceed exception while deleting data](https://www.singlestore.com/forum/t/getting-lock-timeout-exceed-exception-while-deleting-data/4718)

<div class="topic-metadata">

**Author:** [@mnr](https://www.singlestore.com/forum/u/mnr)\
**Replies:** 7\
**Last updated:** [September 16, 2022, 3:57am UTC](https://www.singlestore.com/forum/t/getting-lock-timeout-exceed-exception-while-deleting-data/4718 "2022-09-16T03:57:50Z")

</div>

Getting this below exception while deleting data in batches batch size is 32000 columnstore table with an index created concurrent threads 2 Threads belongs to 2 different indexes exception java.sql.SQLTransientConn…

---

## [Table Limits - Row Size Clarity](https://www.singlestore.com/forum/t/table-limits-row-size-clarity/4786)

<div class="topic-metadata">

**Author:** [@subash](https://www.singlestore.com/forum/u/subash)\
**Replies:** 3\
**Last updated:** [July 25, 2022, 10:47pm UTC](https://www.singlestore.com/forum/t/table-limits-row-size-clarity/4786 "2022-07-25T22:47:12Z")

</div>

On going through the below link I understand the row size can be max 64KB. But need clarity on the below: This limitation is considering all data types? Is the size limitation considering the compression? Table-…

[Next page](https://www.singlestore.com/forum/c/support/query-tuning/13.md?page=1)
