0
votes
2answers
57 views

How to determine the optimal sort_buffer_size?

I read from a sample configuration file which says the following: # Sort buffer is used to perform sorts for some ORDER BY and GROUP BY # queries. If sorted data does not fit into the sort buffer, a ...
1
vote
1answer
44 views

Sorting a list of records alphabetically: is there any reason NOT to let the database do it

I have a list of records that contain a column of arbitrary strings. I would like the application to display them sorted in some order (preferably alphabetically, since humans have to read the data). ...
0
votes
1answer
26 views

Sorting by a field in child table

There are two tables: contract (with primary key contractid) and contractupdate (with field contractid referring to a contract). contractupdate also has DATE field startdate and TINYINT field day. I ...
1
vote
2answers
182 views

Order by and then Concat

I have a table table1 like: col1 col2 A1 A2 A1 B1 A1 B2 A2 A1 A2 B1 A2 B2 B1 A1 B1 A2 B1 B2 B2 A1 B2 A2 B2 B1 I need to concat col1 and col2 such a way that it ...
0
votes
3answers
261 views

MySQL InnoDB Sorting Issue

I was surprised to see MySQL's InnoDB showing an interesting behavior, which I cannot fully explain. According to the official MySQL InnoDB Documentation: "All indexes other than the clustered ...
0
votes
0answers
53 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
361 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
80 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 ...
1
vote
1answer
196 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
422 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
1k 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 ...
2
votes
1answer
101 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
305 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 ...