Questions tagged [limits]
The limits tag has no usage guidance.
80
questions
1
vote
0
answers
28
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 ...
- 135
0
votes
1
answer
53
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
...
- 124
2
votes
1
answer
39
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
51
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
58
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 ...
- 115
0
votes
1
answer
58
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
0
answers
58
views
PgBouncer Pool configurations
I have installed PgBouncer because of too many connections on database server.
How can i check if BgBouncer pool is working correctly.
When i perform SELECT * FROM pg_stat_database order by ...
- 11
0
votes
1
answer
29
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 ...
- 21
0
votes
1
answer
240
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&...
- 3
0
votes
1
answer
23
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 ...
- 111
0
votes
2
answers
64
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 ...
- 65
0
votes
0
answers
28
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 ...
- 153
1
vote
1
answer
92
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/...
- 113
1
vote
3
answers
113
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` ...
- 113
2
votes
1
answer
121
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 ...
- 21
0
votes
1
answer
42
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 ...
0
votes
1
answer
34
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 ...
- 103
0
votes
1
answer
51
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 | ...
- 3
0
votes
1
answer
245
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` = '...
- 105
1
vote
0
answers
22
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 ...
- 113
0
votes
1
answer
66
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 ...
- 103
1
vote
1
answer
16k
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.
...
- 113
2
votes
2
answers
437
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 ...
- 21
1
vote
0
answers
605
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 ...
- 139
0
votes
1
answer
163
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, ...
- 101
0
votes
1
answer
3k
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 ...
- 103
1
vote
1
answer
36
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 ...
- 15
3
votes
1
answer
18k
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 ...
- 131
0
votes
1
answer
85
views
Does switching from INT to BIGINT entails significant downtime?
Hypothetically, if your INT column already has 4 billion rows, how long is the process of converting that column to BIGINT? I know this will depend on server resources but I just want to have a ...
- 159
1
vote
1
answer
195
views
Delete from table using FK columns, JOIN and LIMIT
This query below will never complete, since it always looks at the first 100,000 (user_type, user_ids). How can I write this to delete 100,000 records from the table with the FK reference (Product) to ...
- 121
12
votes
1
answer
9k
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 ...
- 150k
19
votes
2
answers
20k
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 ...
- 293
7
votes
2
answers
5k
views
Postgres slow query with order by, index and limit
I am trying to improve the performance of a postgres(9.6) query. Here is my schema and table contains about 60 million rows.
Column | Type | Modifiers
-------...
- 71
17
votes
5
answers
12k
views
SQL Server Error 8632 due to over 100,000 entries in WHERE clause
My problem (or at least the error message) is very similar to query processor ran out of internal resources - extremely long sql query.
My customer is working with an SQL select-query, containing a ...
- 333
2
votes
1
answer
120
views
Is there a server option which limits the amount of rows returned by a remote query?
I have an issue where a system on our network is making a query (select * from table) which might result in 0 to 70k rows being returned.
For large result sets, say around 50k, the calling system is ...
- 123
1
vote
1
answer
218
views
Is it possible to limit concurrent aggregations in Mongo?
When clients makes certain requests, our service sends aggregation queries to Mongo. Some of these aggregations take longer than others.
Usually this is fine, but occasionally multiple heavy ...
- 235
5
votes
1
answer
3k
views
Limits of applock in MS SQL Server
Microsoft SQL Server offers an arbitrary application-defined locking mechanism through the sp-getapplock command and related commands.
The key, or resource_name, is specified as a string, a varchar ...
- 8,966
4
votes
2
answers
32k
views
MySQL LIMIT within JOIN
I have an 1:m association.
symbols table has many rows in company_key_statistics table. company_key_statistics has column createdAt which is timestamp indicating when row has been created.
I need to ...
- 171
4
votes
1
answer
2k
views
PostgreSQL Size Quota on Table or Schema
How do one limit the size of a PostgreSQL table? (or schema)
Limit in either bytes or rows would be OK.
I'm starting to think that there's no easy and obvious solution.
I have a lot of identical ...
- 41
3
votes
1
answer
1k
views
MySQL 5.5 vs 5.6,5.7 above same database same query but different output, limit have duplicated ID
I am using Running same source code and same database for WordPress, only the mysql version is different. It make my pagination post have duplication.
Here is what I found out. Any idea how to solve ...
- 151
1
vote
2
answers
2k
views
Is it bad form to use "LIMIT 1" on queries that should only return one row anyway? [closed]
If you have a query like this:
SELECT * FROM table WHERE primary_key_column = 123 LIMIT 1;
What do you make of the LIMIT 1?
It is technically not necessary, as we are selecting on a unique column. ...
- 53
3
votes
1
answer
2k
views
Limit number of simultaneous queries by user
I am looking for way to allow one user to execute maximum 3 queries at the time. Is there such possibility in Postgres?
Due to some technial difficulties I cant limit number of connections, and the ...
- 33
0
votes
2
answers
3k
views
Row size too large (> 8126)
I have a table with 156 columns and most of them are either VARCHAR(1000) or TEXT.
Now, when entering data into one such row, I get
mysql_error() = Row size too large (> 8126). Changing some ...
- 225
6
votes
1
answer
13k
views
UPDATE Only one row (using update and join)
I'm trying to get a fast, simple sql query to update only one row at a time while using join too.
I have tried LIMIT, but to no success.
Query:
UPDATE
table1
JOIN
table2 ON table2.col=table1.col
...
1
vote
0
answers
64
views
Resource cost of database per tenant
I've read that one big disadvantage of the database per tenant model in multi-tenant applications is the extra resource cost of having many (potentially thousands or tens of thousands) databases ...
2
votes
2
answers
6k
views
mysql max_connections limit reset
Last week I updated mysql max_connections to 1000 from 151 using
use mysql;
set global max_connections = 1000;
Today there was a power outage at the hosting company and the servers shut down and ...
- 396
2
votes
0
answers
248
views
Oracle to SQL server replication recent data filter options
The current setup:
A live application DB is on an oracle server, which has a nightly replication over to an SQL server that users (read me) can use to bash out reporting from.
The issue is due to ...
- 21
2
votes
1
answer
4k
views
In MySQL, what's the most efficient way to query an entire table in chunks?
If I have a table User is it faster to do something like:
SELECT * FROM user WHERE id > ? ORDER BY id LIMIT 10000
(With each subsequent query using the previous last row's id as the param)
Or to ...
- 123
2
votes
2
answers
4k
views
Express edition core limits ambiguity
We have an application that uses MSSync to download data from a server into a client's SQL Server Express 2005. The client's have a dual core with hyperthreading so, a total result of 4 cores.
I have ...
- 123
1
vote
1
answer
1k
views
Will MongoDB exceed the 64000 ulimit threshold?
We are using MongoDB as a newbie and today we have exceeded the ulimit of the system causing chaos and panic all around.
However I have fixed it by increasing the hard and soft limits like below.
...
- 111