Tagged Questions
1
vote
0answers
38 views
ORDER BY optimization issue
I have a query:
SELECT
user_details.id , user_details.first_name , user_details.last_name,
user_accounts.name account_name, jtl0.account_id account_id,
user_details.title , ...
2
votes
0answers
38 views
Why is MySQL order by performance poor within a single partition of a partitioned table?
I have to store some sequence numbered data in MySQL. I have about 300,000 data items per day for about a 10 year span. Let's say the table structure is just sequence number (a big int) and data (a ...
0
votes
0answers
24 views
MySQL poor order by performance on partitioned table [duplicate]
I have to store some sequence numbered data in MySQL. I have about 300,000 data items per day for about a 10 year span. Let's say the table structure is just sequence number (a big int) and data (a ...
0
votes
1answer
38 views
Index on a query with order
I have the following table with >1M rows:
CREATE TABLE `wishlist_place` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`wishlist_id` int(11) DEFAULT NULL,
`place_id` int(11) DEFAULT NULL,
`city_id` ...
0
votes
1answer
47 views
Optimizing ORDER BY for simple MySQL query
I'm trying to optimize this really simple query, and it gives me grief for a day now :(
Seems pretty straightforward, I need to select with a JOIN from 2 tables, and get top X results sorted in a ...
1
vote
2answers
29 views
Order by on an alphanumerical column
I have a column that has strings of the following type:
Miller 10
Allen 20
King 10
....
Jones 100
I try to sort the column based on the numerical part of the data.
I tried the following:
SELECT ...
0
votes
1answer
65 views
Mysql: order by field or select union?
As a newbie, I expected Mysql to return the rows of this query ordered by their ids order in the id clause
select * from test where id in (3,1,2)
Unfortunatelly for me, that's not true. The results ...
0
votes
1answer
56 views
Update one table from another table while sorting that table based on one column
This is the problem I'm trying to figure out in MySQL. We have an old table contains some forms submitted by our users. Somehow, the previous decision was each time a user comes to this survey, a new ...
3
votes
1answer
42 views
Sequential joining of tables in order
Table albums has (among other field) field id.
Table photos has column id, field album which is a foreign key referring to album id and some other fields (which are irrelevant for the question I ...
2
votes
2answers
110 views
Slow MySQL query with Order and Group by
I have a query that is taking a long time to execute (1.13s). The two tables user_articles and articles have around 25,000 records. This is the query:
SELECT `user_articles`.*
FROM `user_articles`
...
6
votes
1answer
204 views
Must an index cover all selected columns for it to be used for ORDER BY?
Over at SO, someone recently asked Why isn't ORDER BY using the index?
The situation involved a simple InnoDB table in MySQL comprising three columns and 10k rows. One of the columns, an integer, ...
1
vote
1answer
159 views
MySql: #1221 - Incorrect usage of UNION and ORDER BY
I have the following query which is intended to retrieve the newest 3 comments for each post in an array of posts.
SELECT * FROM comment AS tbl WHERE sscm_id IN
(
SELECT sscm_id FROM comment AS t1 ...
0
votes
0answers
34 views
Group by collection_item_id and order by products.weight
SELECT
`products`.*
FROM `products`
INNER JOIN collection_items ON products.collection_item_id = collection_items.id
WHERE `products`.`product_type_id` = 1
AND (products.status = ...
2
votes
2answers
113 views
MySQL order by - one column conditional on another column
In MySQL I have a table (simplified for this question)
CREATE TABLE `projects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`priority` int(11) DEFAULT '1000000',
...
0
votes
0answers
99 views
GROUP_CONCAT with ORDER BY , but results are not orderd
SELECT
*,
(SELECT
GROUP_CONCAT(url SEPARATOR '$$' )
FROM project_photos
WHERE project_id = projects.id
ORDER BY priority) AS images
FROM projects
WHERE catID = 2
LIMIT 0,5
above query works fine but ...
1
vote
1answer
427 views
Slow complex query with group/order
I have a problem with a very important query in my application.
It is very slow when adding group and order to it.
So the full query is:
SELECT
m.id,
m.title,
m.title_text,
...
3
votes
1answer
2k views
MySQL: Optimize UNION with “ORDER BY” in inner queries
I just set up a logging system which consists of multiple tables with the same layout.
There is one table for each data source.
For the log viewer, I want to
UNION all the log tables,
filter them ...
1
vote
0answers
177 views
MySQL GROUP BY and ORDER BY giving inconsistent results
I'm currently working with WordPress, and I've added a new meta value which is a number between 0 and 30. Zero is ignored and up to 30 need to be organised in order and only the latest (based on the ...
0
votes
1answer
39 views
Is index like key(timetime) usable when we query: SELECT * … ORDER BY timetime DESC
When we index a timestamp field like timetime. Can we use it for ORDER BY timetime DESC or is it just usable for ORDER BY timetime ASC OR ORDER BY timetime? Does mySQL use this index when doing vise ...
1
vote
1answer
63 views
How to order by 5, 501, 50101, 6, 601, 60101, etc?
I've got a list of ledger account numbers. These numbers go like this:
5 (main account, #5)
501 (sub account of main account 5, #01)
50101 (sub account of this account, #01)
etc.
So if I have a ...
3
votes
1answer
308 views
How to order query results by tag weight in IN('tag1', 'tag2', 'tag3')
Consider a simple query as
SELECT * FROM posts
JOIN tags USING(post_id)
JOIN tag_map USING(tag_id)
WHERE tags.tag IN('tag1', 'tag2', 'tag3')
tag_map is a many-to-many relationship between tables ...
3
votes
3answers
358 views
MySQL: ORDER BY useless?
CREATE TEMPORARY TABLE tt;
INSERT INTO tt; many times
SELECT FROM tt ORDER BY id LIMIT ?,1000;
The temp table is created at the beginning of the script having an "id" auto_increment column (and ...
3
votes
2answers
1k views
MySql: Delete all rows greater than n entries ordered by datetime
Can someone help me with a MySql query to delete all rows greater than n entries ordered by date?
I.e. say I have 1200 rows of data with a timestamp column. I need to order it by date and preserve ...
9
votes
1answer
2k views
Order by column should have index or not?
I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
1
vote
1answer
1k views
Help optimizing MySQL slow query
i'm running a wordpress which needs some mysql optimization, i have a slow query and i would like to get rid of "Using temporary; Using filesort"
query:
EXPLAIN SELECT SQL_CALC_FOUND_ROWS wp_posts.* ...
2
votes
3answers
138 views
Ordering by date and other columns
I have a one table Q&A like below:
CREATE TABLE `totaalconceptterreinonderhoud`.`acties`
(
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`type` int(11) NOT NULL DEFAULT '3' ...
3
votes
4answers
1k views
MySQL : Conditional ORDER BY to only one column
Hello and thanks for taking time to read this question.
I am using MySQL, and I want to sort results using ORDER BY to one specific column, but the results must be ordered according an specific ...
3
votes
3answers
1k views
Want to order on two columns but not the usual way
I have a table as below
ID userID Date
1 2273 22/08/2011
2 2274 24/08/2011
3 2275 26/08/2011
4 2273 26/08/2011
5 2273 26/08/2011
6 2271 26/08/2011
And want result as ...
1
vote
1answer
210 views
Use order from another select clause
So what I'm actually trying to achieve is filter the most voted on stories. The stories are saved in one table, and all of the votes in another one.
I have this statement to get the vid column order ...
7
votes
4answers
2k views
How do I do a complex GROUP BY in MySQL?
I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to be able to group rows together that have an equal key, but I don't want ...
3
votes
1answer
2k views
How do I make MySQL auto increment in random order?
I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to create a new column for each key that would be an integer such that ...
