All Questions
13 questions
1
vote
1
answer
65
views
mysql ORDER BY return order differently
Trying to figure out why mysql's ORDER BY clause can return order differently, e.g.:
The two files are both generated by using mysql on command line with a single command:
mysql mydb -e 'SELECT ...
- 143
0
votes
0
answers
156
views
How can I optimize the view in MariaDB for Order By?
I have a list of products. Each has many tags. Those tags have normal tables, but they also have OLAP tables that present CSV for each product.
For example, a chair can have 'furniture', 'wood', and '...
- 1,505
0
votes
1
answer
104
views
How can I get a valid rank counter?
I'm making a raking system. I want get the line in one request.
Here is a DB fiddle of my complete example: https://www.db-fiddle.com/f/3Com2wnrhaqfiTKqJSZtft/0
In the example, the UUID used is ...
- 181
0
votes
1
answer
144
views
Rank calculation using variables working only sometimes
I have the following code in MariaDB that was working perfectly well yesterday. It calculates a rank and orders by the data by another column:
SET @c = (SELECT COUNT(*) FROM myTable);
SET @rownum = 0;
...
- 101
-1
votes
2
answers
565
views
A quick way to ORDER all IDs randomly in MySQL [closed]
I need to assign random IDs to a table. Thus, I create a mapping table as
CREATE TABLE t2
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
SourceID int(11) unsigned NOT NULL,
UNIQUE INDEX(SourceID),
...
- 4,531
0
votes
3
answers
148
views
What is wrong with this syntax of a multi-table DELETE...ORDER BY?
I have the following query:
DELETE proxies, pxpl FROM proxies
JOIN proxy_xref_proxy_list AS pxpl ON proxies.ID=pxpl.ProxyID
WHERE pxpl.DeactivationTS < (NOW() - INTERVAL 24 HOUR)
ORDER BY pxpl....
- 115
0
votes
1
answer
1k
views
Efficient partition pruning with ORDER BY on same column as PARTITION BY RANGE + LIMIT?
I've set up a table in MariaDB (10.4.5, currently RC) with InnoDB using partitioning by a column of which its value is incrementing-only and new data is always inserted at the end. For this case, ...
- 227
3
votes
2
answers
9k
views
First order by importance then group by section
In this table scheme http://sqlfiddle.com/#!9/101a9c/9/0
id section rev importance
2 1 b 6
1 1 a 5
3 2 c 9
5 3 e 6
4 ...
- 145
1
vote
1
answer
2k
views
MariaDB / mYSQL Order BY PrimaryKey DESC vs DESC set by Default
What do you guys think about:
After each query, I put "Order BY PrimaryKey DESC"
VS
I set "PrimaryKey Sort by DESC Default" and each query I don't need to put Order By there.
Which one will be ...
- 111
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
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
0
votes
1
answer
76
views
Filter and then sort products by availability
I have table with products. Each product can be avaible in 1+ suppliers, so product can be present multiple times.
products
- id_productSupplier (primary key)
- id_product (foreign key)
- ...
- 1
2
votes
1
answer
1k
views
Making MySQL use primary index without hinting or forcing
I have the following db structure:
CREATE TABLE `question` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`level_id` int(11) NOT NULL,
`video_id` int(11) NOT NULL,
`is_active` tinyint(1) NOT NULL,
...
- 133