filesort

MySQL: Avoid filesort when using IN and ORDER BY

Let's suppose I have the following table (let's call it my_table): CREATE TABLE `my_table` ( `table_id` int(10) unsigned NOT NULL auto_increment, `my_field` int(10) unsigned NOT NULL default '0' PRIMARY KEY (`table_id`), KEY `my_field` (`my_field`,`table_id`) ) ENGINE=MyISAM The primary key for my_table is table_id (auto_i...

Using filesort to sort by datetime column in MySQL

hello, I have a table Cars with datetime (DATE) and bit (PUBLIC). Now i would like to take rows ordered by DATE and with PUBLIC = 1 so i use: select c.* from Cars c WHERE c.PUBLIC = 1 ORDER BY DATE DESC But unfortunately when I use explain to see what is going on I have this: 1 SIMPLE a ALL IDX_PUBLIC,DATE NULL ...

MySQL stuck on "using filesort" when doing an "order by"

I can't seem to get my query to stop using filesort. This is my query: SELECT s.`pilot`, p.`name`, s.`sector`, s.`hull` FROM `pilots` p LEFT JOIN `ships` s ON ( (s.`game` = p.`game`) AND (s.`pilot` = p.`id`) ) WHERE p.`game` = 1 AND p.`id` <> 2 AND s.`sector` = 43 AND s.`hull` > 0 ORDER BY p.`last_move` DESC Table struc...

Optimizing MySql query to avoid using "Using filesort"

I need your help to optimize the query to avoid using "Using filesort".The job of the query is to select all the articles that belongs to specific tag. The query is: select title from tag, article where tag = 'Riyad' AND tag.article_id = article.id order by tag.article_id The tables structure are the followi...

Mysql slow query: INNER JOIN + ORDER BY causes filesort

Hello! I'm trying to optimize this query: SELECT `posts`.* FROM `posts` INNER JOIN `posts_tags` ON `posts`.id = `posts_tags`.post_id WHERE (((`posts_tags`.tag_id = 1))) ORDER BY posts.created_at DESC; The size of tables is 38k rows, and 31k and mysql uses "filesort" so it gets pretty slow. I tried to use different ...

SQL Query always uses filesort in order by clause

Hello all I am trying to optimize a sql query which is using order by clause. When I use EXPLAIN the query always displays "using filesort". I am applying this query for a group discussion forum where there are tags attached to posts by users. Here are the 3 tables I am using: users, user_tag, tags user_tag is the association mapping ...

mysql : temporary, filesort with 2 little tables : how can i improve my query ?

CREATE TABLE tableA ( `key` int(11) NOT NULL, `value` int(11) NOT NULL, UNIQUE KEY Akeyvalue (`key`,`value`), KEY Avalue (`value`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE tableB ( `key` int(11) NOT NULL, `value` int(11) NOT NULL, UNIQUE KEY Bkeyvalue (`key`,`value`), KEY Bvalue (`value`) ) ENGINE=InnoDB DEFAULT CHARSET=l...

In Ruby, how do you list/sort files before folders in a directory listing?

I have to following code in ruby: <% files = Dir.glob('/**/*') files.each do |file| puts file end %> It outputs (for example): /dirA/file1.txt /dirA/file2.txt /dirB/file1.txt /file1.txt /file2.txt /subdirA/file1.txt I want it to output it like this: /file1.txt /file2.txt /dirA/file1.txt /dirA/file2.txt /dirB/file1.txt /s...

How can I improve the performance of this MySQL query?

I have a MySQL query: SELECT DISTINCT c.id, c.company_name, cd.firstname, cd.surname, cis.description AS industry_sector FROM (clients c) JOIN clients_details cd ON c.id = cd.client_id LEFT JOIN clients_industry_sectors cis ON cd.industry_sector_id = cis.id WHER...