All Questions
13 questions
0
votes
1
answer
50
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....
- 1
0
votes
1
answer
665
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 ...
- 169
1
vote
0
answers
760
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 ...
- 167
0
votes
1
answer
53
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
3
votes
1
answer
12k
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&...
- 33
7
votes
2
answers
6k
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
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 ...
- 153
2
votes
1
answer
30k
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` (
`...
- 133
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 ...
- 141
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, ...
- 217
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[]...
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 ...
- 1,888
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+ ...
user27290