The sorting tag has no wiki summary.
0
votes
0answers
30 views
Faster way to GROUP or ORDER dynamic fields?
We have this query in our app, that searches a table for a song title using MATCH AGAINST and picks the most relevant results.
The table has around a million results and is growing.
The app has been ...
1
vote
1answer
32 views
Sorting Result state taking very long time
I am using a MySQL 5.1.69-log DB for analysis purpose.
I've designed my database in a star schema. All tables are using InnoDB.
My innodb_buffer_pool_size=2Gb
This is the query I am running :
...
1
vote
0answers
59 views
Can't get rid of filesort on joined query sorting on second table
For some reason, this query:
EXPLAIN SELECT * FROM biz as b INNER JOIN listings as l ON b.id = l.biz_id
WHERE l.pub_id = 14 AND b.cat_id=310 ORDER BY l.level DESC, l.random DESC LIMIT 5;
says it ...
0
votes
2answers
131 views
Estimated Execution Plan SQL Server Sort?
I'm running a query and its taking a age to execute, looking at the execution plan I can see that 51% of the cost is in the SORT? when in the actual query i am not ORDERING BY anything.
Anyone got ...
1
vote
1answer
200 views
db INDEX on ORDER BY FIELD in very simple query does not help - still slow response?
I have very simple query which returns 200K records very slow (20 seconds).
SELECT * FROM TABLE ORDER BY ID DESC
If I do just
SELECT * FROM TABLE
it returns quick result.
I created INDEX on ...
3
votes
2answers
74 views
Algorithmic order of table indexes for table operations
I have not been able to find a clear indication of the impact table indexes have on the algorithmic order of table operations.
I am not interested in the nuts & bolts of specific implementations; ...
2
votes
1answer
75 views
sorting groups of related rows by average values while keeping the groups together
I'm using PostgreSQL 8.4, but would like a standard SQL solution if possible.
Consider the following table.
corrmodel=# SELECT * from data limit 1;
id | datasubgroup_id | datafile_id | ...
0
votes
0answers
64 views
Prefix traverse of a tree
I have a table with tree data as it defined in this question and have to implement traversal of the tree that looks like preorder traversal but instead of root --> left subtree --> right subtree ...
2
votes
3answers
471 views
Slow query on large table with GROUP BY and ORDER BY
I have a table with 7.2 million tuples which looks like this:
table public.methods
column | type | attributes
...
2
votes
1answer
286 views
MongoDB geospatial query with sort - performance issues
I have query (which is very slow ~2,5s):
db.markers.find({ latlng: { '$within': { '$box': [ [ -16, -140 ], [ 75, 140 ] ] } } }).sort({_id: -1}).limit(1000)
When I run explain for this query I get
...
1
vote
1answer
119 views
Index strategy for a Queue Table, getting rid of filesort
I am trying to get rid of a filesort operation (and preferably also optimize a bottleneck query). We are using an innodb mysql table as a queue to process various incoming business activities. It is ...
1
vote
1answer
240 views
ORDER BY on aggregate field on a closure table giving unexpected results
I am updating my companies product database and I'm looking into different ways to do design our faceted navigation "selector" tables. The idea of faceted navigation might look like this:
It's ...
0
votes
1answer
136 views
Sorting Report entries on alphanumeric combination
I am trying to move over a database that was previously stored in an excel workbook. The previous setup had a different worksheet for each bookshelf and the individual shelves were listed at ...
0
votes
1answer
447 views
How can I tell if an index is being used to sort in MySQL?
I have a query with an ORDER BY clause which uses a column which is the last column on an index which is being used in the WHERE clause, essentially of the form:
SELECT
cols
FROM
tables
WHERE
...
3
votes
2answers
832 views
Postgres primary key sorted in the reverse order. Will it use the index efficiently?
My Rails database is backed by Postgres database. As you may know, each table in Rails gets assigned a primary key which is of Integer type and is indexed.
My list views show records in the reverse ...
4
votes
1answer
290 views
Storage order vs Result order
This is a spin-off question from Sort order specified in primary key, yet sorting is executed on SELECT.
@Catcall says this on the subject of storage order (clustered index) and the output order
...
2
votes
1answer
95 views
Are two indexes needed?
Our MySQL database will contain an encyclopedia. The encyclopedia will be shown in pages where every page contains entries starting from a letter.
Which indexes should I use? Should I have two ...
1
vote
1answer
192 views
Is it possible to add index to mysql with multiple column to optimize order by performance?
# I have a simple table
create table t1 (c1 int, c2 int);
# I want to do the following sorting
select * from t1 order by c2 desc, c1 desc;
select * from t1 order by c2 asc, c1 desc;
# add ...
0
votes
1answer
119 views
Postgres Client - save table sorting
Is there a Postgres client (for Windows) that can save customized sorting when viewing tables? Obviously I could write and save a query for this, but perhaps there's a client that can do this for me. ...
0
votes
1answer
117 views
The sort column must be the last column used in the index - MongoDB Indexing Advice
MongoDB docs advices that:
The sort column must be the last column used in the index
Here is the example:
db.foo.ensureIndex({a: 1, b: 1, c: 1})
Good:
find(a=1).sort(a)
find(a=1).sort(b)
...
1
vote
1answer
137 views
Working of window functions and idea window size for window function
I am not able to understand the concept of window functions. How exactly they work and what are the pros and cons of such technique?
I have read that using limit and offset is slow but its still ...
5
votes
1answer
380 views
Execution plan using B+ tree index, but also sorts
I'm using Oracle sqlplus. And I have the following query:
SELECT fooID from foo MINUS
SELECT fooID from bar;
I have created two unclustered B+ tree indexes. One in the field fooIDof the table foo ...
7
votes
5answers
1k views
Oracle sort varchar2 column with special characters last
How can I sort in Oracle a Varchar2 or NVarchar2 column to be in my own custom defined order. Or are any existing options available that will put letters first, then numbers, then all special ...
4
votes
3answers
855 views
Why are NULLs sorted first?
Why is it that when we have a NULL value in a column and we order by the value ascending, the NULLs are sorted first?
select 1 as test
union all
select 2
union all
select NULL
union all
select 3
...
