Skip to main content

Questions tagged [limits]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
353 views

MySQL apply LIMIT and OFFSET to a selected table, rather than to the result set

I have 3 tables as below. mysql> describe IDN_CONFIG_RESOURCE; +---------------+--------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default ...
0 votes
1 answer
1k views

Best practice for SQL table names when using sub namespaces that exceed maximum table name length

I have a MySQL database that includes many tables named according to a namespace that uses sub names and underscores (_ as in a_b_c), such as the following: life_domain_kingdom_phylum ...
14 votes
1 answer
14k views

Why *not* ERROR: index row size xxxx exceeds maximum 2712 for index "foo"?

We have repeatedly seen failing attempts to index columns with values exceeding a maximum size. Postgres 10 has this kind of error message for it: ERROR: index row size xxxx exceeds maximum 2712 for ...
0 votes
1 answer
475 views

Can I set a row-limit for query results that throws an ERROR if the result set exceeds that limit?

I'm looking for a way to protect my database against runaway queries issued by an application. I have control over both the application and the database. It occurs to me that I can protect against two ...
5 votes
2 answers
8k views

How to deal with large offsets in select?

Table jtest with 200k rows, each row contains jsonb { id: "<uuid>", key: <index> } (<index> is integer 1-200k incremented per each row). There is also btree index on data->'key'. ...
0 votes
1 answer
112 views

Why doesn't Postgres apply limit on groups when retrieving N results per group?

I've come across many questions/answers for Greatest/Top N per group type problems that explain how to solve the problem - generally some variation of row_number() or CROSS JOIN LATERAL, but I'm ...
0 votes
1 answer
38 views

Force MySQL to use specific index on ORDER BY clause with LIMIT to satisfy join

MySQL 8.0.30 I have three tables defined: C CT T CT is a linking table (many-to-many between C & T) The query I'm trying to optimize is of the following kind: SELECT C.*, T.* FROM C JOIN CT ON C....
0 votes
1 answer
174 views

'Limit INT OFFSET @variable' throw runtime error in mysql server

I'm trying to solve this problem in Hackerrand The problem asks for the median in a specific column my code: SET @VAE = (SELECT COUNT(*) FROM STATION); SET @VAE1 = ROUND(@VAE/2)-1; SELECT LAT_N FROM ...
0 votes
1 answer
556 views

Why does my LIMITed SELECT fetch the last record of the table, not the first?

I read that to fetch the first table record, you may use LIMIT 1. I tried to apply that knowledge to my JOIN statement and got a diametrically opposite result. It joined the last record of the joined ...
2 votes
1 answer
38 views

How to get the most recent result sets when grouping key and date are in separate tables

Good evening! I'm having some difficulty. I'm trying to obtain the most recent row from a few tables using SQL. I can do this using a loop in code, but it requires a lot of round trips to the database ...
3 votes
1 answer
423 views

How to limit the amount of values within a postgres table which are updated

using the following sample: CREATE TABLE tbl( x integer, y integer ) INSERT INTO tbl SELECT g AS x FROM generate_series(1, 10) AS g; Which gives: x | y ----+--- 1 | 2 | 3 | ...
0 votes
1 answer
216 views

MySQL logging max_questions resource

How would I know in MySQL if the user has exceeded his max_queries per hour? I know that I can see this error on the client side, but I just want to know that before client is reporting to me, ...
0 votes
1 answer
242 views

MariaDB: Limit changes query plan and makes query very slow

It's counter-intuitive and weird to see how adding "LIMIT 2" changes whole plan for MariaDB (version 10.6.7) from using one index and having total time of 2 seconds into another index with ...
0 votes
2 answers
3k views

How to limit the number of rows in a mysql table

How can I set a limit of rows to a mysql table? (I am using php) POSSIBLE METHOD-1: When the user tries to SIGN-UP I can select everything from the TABLE and get the number of ROWS in the TABLE and ...
0 votes
1 answer
718 views

How to limit CPU by Db2 instance?

On Linux/x86_64 I have several Db2 database instances and most of them have one database per instance. Today I have had one "run away instance" that consumed most of the CPU resources of ...
0 votes
0 answers
346 views

MySQL query: multiple select with different conditions and LIMIT for each, but also LIMIT whole query + paginator friendly

I would like to write SQL query (for MySQL) that is able to SELECT x rows by condition1 from table1 y rows by condition2 from table1+join1 z rows by condition3 from table1+join2 But on every page (...
12 votes
1 answer
5k views

ANSI/ISO plans for LIMIT standardization?

Are there currently plans to standardize one best way of limiting the number of results returned by a query? The stack overflow question at Is there an ANSI SQL alternative to the MYSQL LIMIT ...
2 votes
3 answers
69 views

How do I block all IP addresses from connecting remotely to Cassandra?

I want access to remote cassandra with one ip address. how can I change cassandra.yaml to block all ip address except ip that I want to connect? Can I do by cassandra.yaml ?
3 votes
2 answers
24k views

Alternate to use LIMIT in subquery in mysql?

I have 2 tables, Resource and views and I'm trying to get the list of all the items from Resource table whose ran_resId don't match with the ran_resId of last 2 records of views table. I'm trying to ...
1 vote
0 answers
271 views

Mysql self Join query with Limit never terminates

I'm running an experiment with the query Select Distinct table_1.id1, table_2.id1 FROM image as table_1, image as table_2 WHERE table_1.id2 = table_2.id2 LIMIT K; When I run the query ...
3 votes
2 answers
27k views

Limit left join for multiple rows

Since MySql doesn't support main alias reference in subquery is a bit challenging to create query with multiple rows where you have to limit rows of left join tables. Let's overview simple example. ...
1 vote
0 answers
678 views

Very slow MariaDB query when adding ORDER BY/LIMIT but there are no rows

I am facing an issue with MariaDB 10.1 on a log table with the following (simplified) structure: CREATE TABLE logs ( log_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, log_date TIMESTAMP DEFAULT ...
2 votes
1 answer
3k views

Limiting the number of records from nested INNER JOIN table

The context is as follows: We have 3 tables: We'll call them A,B and C. A has a foreign key referencing table B, we'll call it "bId". The relationship is a ManyToOne. (One record in B can ...
0 votes
1 answer
479 views

data model limit rows per user

This is regarding to postgres that I was asked to create data model which has a relationship with user. For example a new data model called Comment M:1 User, but while creating the data model is it ...
0 votes
1 answer
196 views

mySQL Query with a LIMIT on a row value

I am trying to replace a lot of queries like this with a single query. SELECT * FROM y WHERE x = a ORDER BY timestamp LIMIT 50 SELECT * FROM y WHERE x = b ORDER by timestamp LIMIT 50 SELECT * FROM y ...
0 votes
1 answer
175 views

Determine Available Blocks of Contiguous Ids

We have a SQL Server table that has an int autoincrement primary key column on it. The table's primary key space is fragmented. For example, ids 1 through 10 are in use but then ids 11 through 100,000 ...
0 votes
1 answer
52 views

Fill remaining LIMIT rows selection if conditions met are less than LIMIT number

I have a MySQL database with data in it. They all have locations (London, Liverpool, etc), I want a query to select 10 random rows with a location (for example London), and if there aren't enough to ...
3 votes
1 answer
10k views

How to use ORDER BY and LIMIT inside JSON_AGG

I have a query which returns a desired output. SELECT shop, JSON_AGG(item_history.* ORDER BY created_date DESC) as data FROM item_history GROUP BY shop; Results: [ { "shop&...
0 votes
1 answer
49 views

MySQL replication username

What is the difference in using these two approaches when setting master-slave replication: Setting the binlog-do-db option on the master to choose which DBs are going to be replicated; Creating a ...
0 votes
2 answers
548 views

How to limit tree traversal in case of loops?

So I want to limit tree depth traversal (as the simplest recursion killer tool). Example code that turns normalized records into tree: CREATE TABLE items ( item_id serial PRIMARY KEY, title ...
2 votes
1 answer
29k views

Query with order by, group by and limit

I have two tables: chains and documents. CREATE TABLE `chains` ( `chain_id` int(10) unsigned NOT NULL AUTO_INCREMENT, ... PRIMARY KEY (`chain_id`), ... ) ENGINE=InnoDB CREATE TABLE `docs` ( `...
0 votes
0 answers
438 views

postgresql, connection limit for different roles

Is possible setup in postgresql.conf connection limit for specified role globally (in some .conf file) or individually? For example, I have 3 roles and max_connections = 600. Need to setup max 10 ...
1 vote
1 answer
773 views

LIMIT and OFFSET a complex query

I have a query generated by a ORM (Sequelize), i ran into an issue where sequelize fails, look at those issues. https://github.com/sequelize/sequelize/issues/7344 https://github.com/sequelize/...
1 vote
3 answers
922 views

How to not allow to insert a new record if count of current records exceeds a specified limit

I have two tables as the following team `id` BIGINT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `member_limit` INT NOT NULL team_member `id` BIGINT PRIMARY KEY AUTO_INCREMENT, `team_id` ...
0 votes
1 answer
106 views

updating n number of records mysql database

I want to update n number of records in table using a query like below, but it will return an error since in mysql, it is not allowed to use value after the limit key update producedItem pi inner ...
2 votes
2 answers
849 views

Predicting the primary keys exhaustion in MySQL based on rate of insertion

We are running out of primary keys for our table in production. We do not want that to occur again. So we need a mechanism to alert us when we are running out of primary keys based on the rate of ...
20 votes
1 answer
23k views

"Limit 1000,25" vs "limit 25 Offset 1000"

Recently I've found out that MySQL has a offset feature. I've been trying to find documentation about the results of offset, or the difference in between offset and the limit variant, but I can't seem ...
0 votes
1 answer
71 views

Display Top X results for each unique value

I am trying to figure out how I can output any machine that has had the latest 3 backups be larger than 10GB. My SQL Table looks similar to the below table: ComputerID | BackupImageName | ...
0 votes
1 answer
1k views

How does adding LIMIT work with Where conditions in mysql?

In my database, there is only one record which satisfies the condition iv_title = 'ball skills'. Now I'm executing the following query : SELECT * FROM `imageAndVideo` WHERE `iv_title` = '...
1 vote
0 answers
23 views

Set Maximum Tables In A Database [duplicate]

I need to set up a MySQL database with a maximum number of tables which can be created in it. For example, if I have my maximum at 5 tables for a single database, and someone attempts to create a 6th ...
1 vote
1 answer
839 views

Distinct result with row_id for limit query

There are 2 tables: CREATE TABLE sample1 ( id int, name varchar, score int, mode int ) CREATE TABLE sample2 ( id int, mode int ... ) I need to select distinct name with MIN(score) and ...
11 votes
3 answers
18k views

COUNT(*) gives more than 1 with LIMIT 1?

I'm trying to count old records. Why does Postgres give a result of 1160, even though I set some limit, LIMIT 1 in this case? SELECT COUNT(*) FROM data WHERE datetime < '2015-09-23 00:00:00' LIMIT ...
3 votes
1 answer
2k views

Does LIMIT 1 still have effect on a unique column

I know that adding LIMIT 1 when searching for only one record makes a lot of difference when it comes to performance. But does it still have any effect when we search a column with a unique constraint?...
0 votes
2 answers
20k views

What is the query to get the process limit on Oracle 11g?

I'm getting the error: Maximum Number Of Process Exceeded I want to see (using a query) what the setting is in my database for the process limit. My question is: What is the query to get the ...
20 votes
2 answers
25k views

MongoDB terminates when it runs out of memory

I have the following configuration: a host machine that runs three docker containers: MongoDB Redis A program using the previous two containers to store data Both Redis and MongoDB are used to ...
1 vote
0 answers
860 views

SQL Server Express 2017 database size limitation

I have a database hosted on SQL Server Express 2017. The size was small like 200MB. Today I have copied 24GB tables to the same database from external database hosted on different server. The ...
1 vote
1 answer
188 views

MySql LIMIT + JOIN is stopping data from being returned?

I originally posted this question on stackoverflow, but then remembered there's a stackexchange specifically for databases! I've got two tables and am trying to return all profiles that are assigned ...
0 votes
1 answer
6k views

Limit groups of results based on a column value

I'm not sure I formulated the question properly, but anyway. I have a table with level-2 comments, some of which, expectedly, have the same comment_parent column value. How do I make a SELECT query ...
1 vote
1 answer
49 views

What happens if I load last 10 rows then next 10 after other rows are inserted?

What if I had a PHP script that had a pagination that loads last 10 rows then page two loaded next 10 in DESC order, but before loading next 10 on page 2, other 10 rows were added to the table? What ...
3 votes
2 answers
7k views

MySQL failed connection attempts hitting max_connections

I run a couple of websites but I believe one of them is causing too many Connections Failed Attempts that causes max_connections variable to hit. Currently, max_connections=2500 (already large) but ...