[RESOLVED] Can't get rid of filesort and "using where"

lpa

mysql> explain SELECT id FROM multimedia WHERE approved='1' and city ='5' order by id limit 0,15;
+----+-------------+------------+------+---------------+--------------+---------+-------------+------+-----------------------------+
| id | select_type | table      | type | possible_keys | key          | key_len | ref         | rows | Extra                       |
+----+-------------+------------+------+---------------+--------------+---------+-------------+------+-----------------------------+
|  1 | SIMPLE      | multimedia | ref  | approved      | aproved      | 4       | const,const | 1479 | Using where; Using filesort |
+----+-------------+------------+------+---------------+--------------+---------+-------------+------+-----------------------------+

1) "id" is the primary key
2) I have index on both 2 columns - "approved" and "city".

Any ideas?

bradgrafelman

Can you show us a 'SHOW CREATE TABLE multimedia' output?

lpa

Here is it:

CREATE TABLE `multimedia` (
  `id` bigint(20) NOT NULL auto_increment,
  `city` mediumint(5) NOT NULL,
  `description` text NOT NULL,
  `approved` tinyint(1) NOT NULL,
  `filename` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `approved` (`approved`,`city`),
  FULLTEXT KEY `description` (`description`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1754 ;

jazz_snob

Reading this http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html I came up with a hack. Change this line in the table create (or add another index to the table):

KEY `approved` (`id`, `approved`,`city`),

Why? I think there is an issue w/ having FULLTEXT KEY and id KEY. By making id a "part" of the "approved" index (or some other index) it then can be used to satisfy the order by (this is my theory).

However, since I never use myisam I cannot offer any more insight as I am not familiar with use of fulltext.

lpa

jazz_snob, thank you for your reply.

But "id" is the primary key. Is it OK to include primary key column in other indexes?

jazz_snob

lpa;10958789 wrote:
But "id" is the primary key. Is it OK to include primary key column in other indexes?

Yes its ok, but if I were doing this and wanted to avoid the file sort I'd do the sorting with php instead of mysql, especially if its a limited number of rows (<1000).

lpa

jazz_snob, I will have at least 200 000 rows and the number will grow. I don't think that it's a good idea to sort with php.

I will try change the index as you suggested. Thanks you.

I love this forum because I always get some answer/solution. I have posted some questions in forums.mysql.com, but never get any replies. 🙁

jazz_snob

My reference to using PHP to sort, rather than mysql, is applicable when for example you only want to load a portion of the rows from the table, like if you are displaying 100 records per page in a paginated view.

There is a performance hit when using order by w/ mysql, esp. if file sort is involved.

From http://radar.oreilly.com/2010/03/joe-stump-talks-location-and-n.html :

For instance, we rewrote comments about a year-and-a-half ago, and we switched from doing the sorting on a MySQL front to doing it in PHP. We saw a 4,000 percent increase in performance on that operation.

lpa

jazz_snob, yes, I want to load a portion of the rows from the table and that's why I don't understand how is it possible to use sorting with php.

If I load all rows, I can sort them using sort(); function. But I don't know how can I load a portion of rows (last) without using "order by ... DESC"

jazz_snob

You know what, you're right. I can't think of how to avoid the order by in this case. However...You want the last orders first? Which means in this case the orders with the highest ids because you used auto increment. One way to do this would be to know how many rows were in the table, then use limit and offset to grab the last chunk of rows. For example if there are 2000 rows and you want the latest 100: select * from table limit 100 offset 1900. Then you could sort those 100 rows with php to order the records most recent first.

lpa

jazz_snob, thank very much you for your ideas.

I created a query without "order by" and then I sort using php in ascending or descending order.

Everything works very good and "explain" doesn't show "using filesort", "using temporary" anymore.

But this query with "offset" doesn't contain any "order by". Can I be sure that it will always select the last/first rows from table and not rows in some haotic order?

If I check a table in Php MyAdmin, sometimes the last added rows are somewhere in the moddle of the table. I don't understand if it's an Php MyAdmin issue or the rows are really in the moddle of the table. If it's not only in Php MyAdmin but the rows really can be in haotic order, I will need to use "order by" anyway.

bradgrafelman

I can't find the MySQL document I recall, but basically if you don't use an "ORDER BY" clause in your query, then you shouldn't expect results to be in any particular order (e.g. oldest to newest) - even if they appear to be in a certain order 'most' of the time.

lpa

thank you bradgrafelman.

I need to display rows in the order how they have been inserted into DB. I can sort them by php, but I need rows not in chaotic order.

So, as I understand, the only way for me is to add "id" column to each index where query uses "order by id" statement.

explain SELECT id FROM multimedia WHERE approved='1' and city ='5' order by id limit 0,15;

Result without "id" column in the index:

+-----------------------------+
| Extra                       |
+-----------------------------+
| Using where; Using filesort |
+-----------------------------+

"using filesort" appears not depending of the order - ASC or DESC.

Result with "id" column in the index:

+-------------+
| Extra       |
+-------------+
| Using where |
+-------------+

jazz_snob

I think its a safe assumption that mysql will by default order records randomly in the result set, and not necessarily by insert order. So the idea w/ offset probably wasn't very robust.

lpa

Thanks to all. I added ORDER BY column to the index and now the "using filesort" is gone.