List:General Discussion« Previous MessageNext Message »
From:Lay András Date:June 12 2014 10:59am
Subject:Order column in the second table
View as plain text  
Hi!

I have two tables:

CREATE TABLE `egyik` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `duma` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `e_idx` (`duma`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `egyik` VALUES (1,'aaa'),(2,'bbb');

CREATE TABLE `masik` (
  `id` int(10) unsigned NOT NULL,
  `szam` int(10) unsigned NOT NULL,
  KEY `m_idx` (`id`,`szam`),
  CONSTRAINT `masik_ibfk_1` FOREIGN KEY (`id`) REFERENCES `egyik`
(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `masik` VALUES (1,7),(2,6);

I execute this query:

(root@localhost) [test]> explain select e.id from egyik e,masik m
where e.id=m.id and e.duma='aaa'order by m.szam asc;
+------+-------------+-------+------+---------------+-------+---------+-----------+------+-----------------------------------------------------------+
| id   | select_type | table | type | possible_keys | key   | key_len
| ref       | rows | Extra
        |
+------+-------------+-------+------+---------------+-------+---------+-----------+------+-----------------------------------------------------------+
|    1 | SIMPLE      | e     | ref  | PRIMARY,e_idx | e_idx | 767
| const     |    1 | Using where; Using index; Using temporary; Using
filesort |
|    1 | SIMPLE      | m     | ref  | m_idx         | m_idx | 4
| test.e.id |    1 | Using index
        |
+------+-------------+-------+------+---------------+-------+---------+-----------+------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

How can i eliminate the "Using temporary" and "Using filesort" messages?

Thank you,

Lay
Thread
Order column in the second tableLay András12 Jun 2014
  • Re: Order column in the second tableAntonio Fernández Pérez12 Jun 2014
    • Re: Order column in the second tableLay András12 Jun 2014
      • Re: Order column in the second tableyoku ts.13 Jun 2014
        • Re: Order column in the second tableLay András13 Jun 2014