Something else to consider here: MySQL can use indexes to optimize order by as well, but your order by value can't be indexed since it is derived from a database lookup/calculation. If you really need to order by "diferenca" you may have to live with it, although you may still see "Using filesort"(which would indicate a slower query) in your explain since MySQL may have to make a second pass to sort the order by.
Check out: http://www.mysql.com/doc/en/ORDER_BY_optimisation.html http://www.mysql.com/doc/en/MySQL_indexes.html >>> "Dobromir Velev" <[EMAIL PROTECTED]> 06/13/03 10:03AM >>> Hi, You need to index the column that is used in the where clause try this ALTER TABLE tempo_resposta ADD INDEX idmaquina (idmaquina); HTH Dobromir Velev ----- Original Message ----- From: "Leonardo Rodrigues Magalhães" <[EMAIL PROTECTED]> To: "MySQL ML" <[EMAIL PROTECTED]> Sent: Friday, June 13, 2003 17:21 Subject: index question > > Hello Guys, > > I have the following table: > > CREATE TABLE tempo_resposta ( > id int(11) NOT NULL auto_increment, > idmaquina int(11) NOT NULL default '0', > tempo int(11) NOT NULL default '0', > horario datetime NOT NULL default '0000-00-00 00:00:00', > PRIMARY KEY (id) > ) TYPE=MyISAM; > > In this table, I'll be running this query: > > select tempo,unix_timestamp(now())-unix_timestamp(horario) as diferenca from > tempo_resposta where idmaquina=SOMEID order by diferenca desc > > Right now, seems table is being completly scanned with this query: > > mysql> explain select tempo,unix_timestamp(now())-unix_timestamp(horario) as > diferenca from tempo_resposta where idmaquina=23 order by diferenca desc; > +----------------+------+---------------+------+---------+------+------+---- > -------------------------+ > | table | type | possible_keys | key | key_len | ref | rows | > Extra | > +----------------+------+---------------+------+---------+------+------+---- > -------------------------+ > | tempo_resposta | ALL | NULL | NULL | NULL | NULL | 9216 | > Using where; Using filesort | > +----------------+------+---------------+------+---------+------+------+---- > -------------------------+ > 1 row in set (0.00 sec) > > rows=9216, exactly all rows in the table > > > Question: is there a way of creating an index for helping that kind of > query ? I've tried creating index on horario, but it doesnt helped. > > > Sincerily, > Leonardo Rodrigues > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
