The tag has no usage guidance.

learn more… | top users | synonyms

1
vote
3answers
20 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 ...
0
votes
1answer
34 views

Speed up ORDER BY and LIMIT in MySQL

I have a table with contact records, and clients wish to search this table to find records with a matching email address (filtered by account ID). The results are paginated by a RESTful API. This ...
18
votes
4answers
3k 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
0answers
16 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?
0
votes
2answers
60 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
29 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
192 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 ...
0
votes
1answer
57 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
2answers
138 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 ...
2
votes
2answers
195 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, ...
1
vote
2answers
227 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 ...
1
vote
1answer
542 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
96 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
1k 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 ...
1
vote
1answer
123 views

Requirement of SQL Server 2008

The software I am installing requires SQL Server 2008. I have SQL Server 2008 R2 Express Version. I am having problem in the setup and finally I am arriving on the conclusion that my SQL Express can ...
0
votes
2answers
42 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 ...
1
vote
2answers
622 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'...
0
votes
1answer
2k 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
265 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
131 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 ...
15
votes
7answers
19k views

Too many columns in MySQL

I have a table with 1699 columns and when I'm trying to insert more columns I get the too many columns in the table error. In this table I have only 1000 rows. For me the most important thing is the ...
1
vote
3answers
142 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'),...
2
votes
1answer
4k 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 ...
2
votes
3answers
2k 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.
0
votes
1answer
705 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
2answers
289 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, ...
5
votes
3answers
15k 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 ...
1
vote
1answer
6k 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+ ...
0
votes
1answer
100 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` ...
0
votes
2answers
1k views

limit and then group by in MySQL

I have a mysql table having following structure : payment_id(int auto_increment), emp_id(int), chargeTime(datetime), payment(double) I need to have three latest (with respect to chargeTime) ...
2
votes
2answers
325 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 ...
6
votes
1answer
1k 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 ...
5
votes
2answers
409 views

What are sensible/rational character limits in a database e.g. for a persons name

This is a general question about database admin. Is there some kind of standardization for character limits on fields in a DB? It makes sense to specify character limits on the database end for names,...