Questions tagged [limits]
The limits tag has no usage guidance.
1
vote
1answer
27 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 ...
1
vote
1answer
27 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
1answer
41 views
How to determine queries per hour executed by a particular MySQL user?
I am trying to implement user based quota system in MySQL to limit MAX_QUERIES_PER_HOUR for a particular user. I have an option to set this limit via MAX_UPDATES_PER_HOUR 10.
But my problem is that I ...
0
votes
1answer
33 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 ...
1
vote
1answer
31 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 ...
0
votes
1answer
349 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 ...
8
votes
2answers
4k 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 ...
6
votes
2answers
1k 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
-------...
16
votes
5answers
7k 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 ...
2
votes
1answer
71 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 ...
1
vote
1answer
83 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 ...
3
votes
1answer
1k 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 ...
4
votes
2answers
8k 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 ...
4
votes
1answer
609 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 ...
4
votes
1answer
733 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 ...
1
vote
2answers
880 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. ...
1
vote
1answer
591 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 ...
0
votes
2answers
2k 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 ...
4
votes
1answer
5k 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
0answers
46 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 ...
3
votes
2answers
2k 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 ...
2
votes
0answers
243 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 ...
1
vote
1answer
1k 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 ...
3
votes
2answers
2k 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 ...
0
votes
1answer
387 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.
...
1
vote
1answer
2k 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` (
`...
1
vote
3answers
30 views
Why is determining existence (or not) of rows matching certain criteria using a 'LIMIT 1' query on indexed columns examining more than 1 row?
Consider a table created like this:
CREATE TABLE `someTable` (
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
`owner_id` bigint(11) unsigned NOT NULL,
`device_id` bigint(11) unsigned NOT ...
21
votes
4answers
16k views
How to limit maximum number of rows in a table to just 1
I have a configuration table in my SQL Server database and this table should only ever have one row. To help future developers understand this I'd like to prevent more than one row of data being added....
0
votes
1answer
295 views
How are Postgres partitions counted for the table size limit?
Postgres has a limit of 32T in a single table, but what if the table is partitioned - is that 32T per partition now?
2
votes
2answers
3k 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 ...
1
vote
1answer
114 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 ...
1
vote
1answer
431 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?...
2
votes
2answers
951 views
PostgreSQL How to optimize a query with ORDER BY and LIMIT 1?
I have the following PostgreSQL schema:
CREATE TABLE User (
ID INTEGER PRIMARY KEY
);
CREATE TABLE BOX (
ID INTEGER PRIMARY KEY
);
CREATE SEQUENCE seq_item;
CREATE TABLE Item (
ID ...
3
votes
2answers
4k views
ORDER BY indexedColumn ridiculously slow when used with LIMIT on MySQL
The MyISAM table has 4.5m rows. Query with just LIMIT returns fast; query with just ORDER BY returns fast; combining both it never finishes. I've also tried USE INDEX(indexedColumn, Latitude, ...
5
votes
2answers
4k 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
1answer
3k views
Query with limit and order by runs too slow
I have the following table:
CREATE TABLE dpg2
(
account_id integer NOT NULL,
tank_id integer NOT NULL,
battles integer,
dmg integer,
frags integer,
wins integer,
recent_battles integer[]...
3
votes
2answers
121 views
How does TOPN influence a query with group by?
I have a query that looks like the following:
Select t1.field1, t2.field2, sum(t1.field3)
From t1 inner join t2 on t1.id = t2.id
Group by t1.field1, t2.field2
It's simplified, normally there are a ...
1
vote
3answers
3k views
Get rows changed between two dates (latest and previous)
I have a table _sync_history with this data:
+----+----------------+---------------------+
| id | object_name | sync_time |
+----+----------------+---------------------+
| 1 | territory ...
0
votes
2answers
45 views
Which query performs better when searching a row where a column has maximum?
Consider a simple table for example purposes:
person(name varchar, age int)
I have a task to retrieve the oldest person. Which query is better in general for such problems?
Query 1: criteria with ...
0
votes
2answers
11k 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 ...
1
vote
1answer
798 views
Select with limit, get total count, optimize query [closed]
The site mimics the "likes" tooltip from Facebook, the only difference is that you can like or dislike the content.
The tooltip shows the first 12 users that rated the content and displays the number ...
1
vote
2answers
140 views
Is there a more efficient way to set up this table
I'm looking to add a new feature in our production site that will show users 25 items they most recently viewed. I already record every view into various tables based on the item type, but querying ...
1
vote
3answers
216 views
Longest matching suffix while using aggregate functions
Background
Looking to find the longest matching string suffix.
Setup
Consider the following fiddle:
CREATE TABLE noun
("label" varchar(10))
;
INSERT INTO noun
("label")
VALUES
('bar'),...
1
vote
2answers
2k views
MySQL/MariaDB Limitations
we're running MariaDB on our DB server. We have to install multiple databases on each DB instance (in fact each DB server runs multiple MariaDB instances) because each database must be separated.
We'...
1
vote
1answer
366 views
Can having a LIMIT on DELETE degrade performance dramatically?
I have a database project in php and mysql. For, hopefully redundant, data security I added a LIMIT 1 to the delete($uniqueID) php function we use.
Nobody cared because records really aren't ...
11
votes
1answer
15k 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 ...
1
vote
1answer
2k views
Maximum number of fields/columns in MongoDB
I'm considering learning and using MongoDB for a project, but I'm going to need to be able to store at least 1024 columns for the first part, and as many as 1048577 for the second. Does MongoDB have a ...
2
votes
3answers
5k views
How to limit MongoDB database size
Are there any mechanisms to limit the size of a single database on MongoDB 2.6? I found some options like quota, quotaFiles and nssize but am unsure how to use them.
2
votes
2answers
573 views
Limiting the Number of Records Joined
How can I perform a query that contains a (one-to-many) join between two tables, which will only join up to a pre-defined number of joined records?
So, lets say I have two tables, one for Matches, ...
1
vote
1answer
14k views
SQL Limit + count group?
I'm tracking all the visitors of my site using this table:
Visitors table
In my admin panel, I have an overview of which countries visited my site, where they came from, ..., but because over 20+ ...
