Skip to main content

Questions tagged [limits]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
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 ...
Shiro's user avatar
  • 153
1 vote
2 answers
3k 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. ...
Tony H's user avatar
  • 153
3 votes
1 answer
3k 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 ...
amonowy's user avatar
  • 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 ...
anjanesh's user avatar
  • 279
7 votes
1 answer
16k 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 ...
Abdelhafidh Belalia's user avatar
1 vote
0 answers
71 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 ...
just.another.programmer's user avatar
3 votes
2 answers
7k 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 ...
Roy Hinkley's user avatar
2 votes
0 answers
266 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 ...
Dave Webster's user avatar
2 votes
1 answer
6k 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 ...
Jordan0Day's user avatar
2 votes
2 answers
5k 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 ...
Thomas's user avatar
  • 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. ...
WickStargazer's user avatar
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` ( `...
smie's user avatar
  • 133
1 vote
3 answers
50 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 ...
KittenOverflow's user avatar
26 votes
4 answers
38k 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....
Dib's user avatar
  • 447
0 votes
1 answer
1k 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?
Gaius's user avatar
  • 11.2k
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 ...
Asad Moeen's user avatar
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 ...
James111's user avatar
  • 250
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?...
Wilt's user avatar
  • 161
4 votes
2 answers
3k 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 ...
Ivaylo Toskov's user avatar
4 votes
2 answers
9k 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, ...
Caleb Pitman's user avatar
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 ...
happy_marmoset's user avatar
4 votes
1 answer
7k 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[]...
Spencer McLaughlin's user avatar
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'. ...
user606521's user avatar
  • 1,375
3 votes
2 answers
156 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 ...
kamahl's user avatar
  • 133
1 vote
3 answers
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 ...
ReynierPM's user avatar
  • 1,764
0 votes
2 answers
46 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 ...
Zoltán Tamási's user avatar
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 ...
hawkeye's user avatar
  • 167
1 vote
1 answer
2k 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 ...
Lando's user avatar
  • 123
1 vote
2 answers
161 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 ...
user16081's user avatar
  • 113
1 vote
3 answers
411 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'),...
Dave Jarvis's user avatar
1 vote
2 answers
3k 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'...
Unkl Benz's user avatar
1 vote
1 answer
460 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 ...
Gn13l's user avatar
  • 133
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 ...
Martijn's user avatar
  • 303
2 votes
1 answer
4k 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 ...
Qaz's user avatar
  • 203
2 votes
3 answers
8k 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.
ivica's user avatar
  • 185
2 votes
2 answers
2k 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, ...
topherg's user avatar
  • 163
1 vote
1 answer
28k 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+ ...
user avatar
0 votes
1 answer
196 views

MySQL limits on huge number return zero rows

I have table about 1,000,000 rows. this query work well: SELECT * FROM `articles` WHERE `articles`.`hash` NOT IN ( '1z8y' ) LIMIT 2000,10 But this query return zero rows: SELECT * FROM `articles` ...
Mohammad Hossein Fattahizadeh's user avatar
10 votes
3 answers
33k views

How to check if I am hitting the Express Edition size limit?

I am confused. AFAIK SQL Server 2005 Express has a limit of 4GB database data size. However I have the following results from sp_spaceused: How can I check if my DB is hitting the size limit? Is ...
g00fy's user avatar
  • 211
2 votes
2 answers
347 views

Why does adding LIMIT to my query make it crawl?

Simple query: select sum(score) total,name,gender,dob,country from users join scores on users.id = scores.user_id where date between '2012-01-01' and '2012-01-31 23:59:59' group by scores.user_id ...
Apemantus's user avatar
  • 225
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 ...
chucksmash's user avatar

1
2