New answers tagged

0

The explain depends on which column you using in WHERE clause, the column which are large in size that which doesn't fit in SORT BUFFER requires filesort for sorting purpose. And other point is b.id may be primary key which is sorted by default, So it doesn't requires to do sorting & where c.table_b_id requires sorting.


1

Mike D. has provided an answer on how SQL Server sorts data that might help you understand what is it about the deterministic result you see on some comments. The Arguments of the ORDER BY doc says: ASC | DESC Specifies that the values in the specified column should be sorted in ascending or descending order. ASC sorts from the lowest value to highest value....


1

But the data in your order by clause doesn't provide any ordering verification_code is null and all values for created_date are the same you have to give the engine a little bit to work with


1

I wouldn't worry about hardcoding the number of months in a year create table #t (mm int) insert #t (mm) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) select mm, (mm + 8) % 12 from #t order by 2 asc


1

Investigate the plan. MySQL alters the order of the tables scanning. Add STRAIGHT_JOIN. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=592b1fb77c5c383abb8d93f9d9e13559


1

That sounds simple: ORDER BY array_position(pillars, 'c'), st_distance(....)


0

I did a lot of experimentation by checking the performance. It may help others. The fastest way (by far) is to do the random rearrangement outside the SQL query. SELECT ID INTO OUTFILE '/tmp/id.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM t1 then shuf -o /tmp/id.csv < /tmp/id.csv and finally the fast INSERT step LOAD DATA LOCAL ...


2

Possible trick. Create intermediate table. Copy your ID from t1 into it. Add virtual generated column which calculates some hash from id value, and index it. Use this table as a source for insertion, add sorting by created index expression, and force it (without index hint it may be ignored due to 100% rows selection.. from the other side, it must be used ...


Top 50 recent answers are included