Hiya

Im wondering if someone could help me understand this. If you look at my
two queries below. By the ORDER BY one is using ` and the other ', as a
result, if you do an explain you will see that the top query does a
filesort, while the other does not.

Would anyone know why.

mysql> explain SELECT `Contact`.`id`, `Contact`.`name`,
`Contact`.`surname`, `Contact`.`mobile`, `Contact`.`user_id`,
`Contact`.`active`, `Contact`.`created` FROM `contacts` AS `Contact`
WHERE `Contact`.`user_id` = 10203 AND `Contact`.`active` = '1' ORDER
BY `name` asc LIMIT 10;
+----+-------------+---------+------+---------------+------+ ---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+---------+------+---------------+------+ ---------+------+--------+-----------------------------+
| 1 | SIMPLE | Contact | ALL | NULL | NULL | NULL |
NULL | 344709 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+ ---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain SELECT `Contact`.`id`, `Contact`.`name`,
`Contact`.`surname`, `Contact`.`mobile`, `Contact`.`user_id`,
`Contact`.`active`, `Contact`.`created` FROM `contacts` AS `Contact`
WHERE `Contact`.`user_id` = 10203 AND `Contact`.`active` = '1' ORDER
BY 'name' asc LIMIT 10;
+----+-------------+---------+------+---------------+------+ ---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+---------+------+---------------+------+ ---------+------+--------+-------------+
| 1 | SIMPLE | Contact | ALL | NULL | NULL | NULL |
NULL | 344710 | Using where |
+----+-------------+---------+------+---------------+------+ ---------+------+--------+-------------+
1 row in set (0.00 sec)

Thanks
Brent

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2 [at] m.gmane.org

On Wed, 30 Mar 2011 10:05:50 +0200
Brent Clark <brentgclarklist [at] gmail.com> wrote:

> Hiya
>
> Im wondering if someone could help me understand this. If you look at my
> two queries below. By the ORDER BY one is using ` and the other ', as a
> result, if you do an explain you will see that the top query does a
> filesort, while the other does not.
>
> Would anyone know why.
>
> mysql> explain SELECT `Contact`.`id`, `Contact`.`name`, .... ORDER
> BY `name` asc LIMIT 10;
this orders by the column `name`, as you expect.
>
> mysql> explain SELECT `Contact`.`id`, `Contact`.`name`, .... ORDER
> BY 'name' asc LIMIT 10;

When you use quotes you are no longer referring to the column, instead the order orders by the string 'name'.
This is a meaningless sort, and your results wil not be ordered at all.

>
> Thanks
> Brent

--
Simcha Younger <simcha [at] syounger.com>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2 [at] m.gmane.org

On 30/03/2011 09:05, Brent Clark wrote:
> Hiya
>
> Im wondering if someone could help me understand this. If you look at my
> two queries below. By the ORDER BY one is using ` and the other ', as a
> result, if you do an explain you will see that the top query does a
> filesort, while the other does not.

Because column names either need to be unquoted or enclosed in
backticks. If you put a string inside ordinary quotes (either single or
double) then it's treated as a string variable. And you can't sort by a
string variable.

As a demonstration, try these:

SELECT * FROM contacts LIMIT 10
SELECT * FROM `contacts` LIMIT 10
SELECT * FROM 'contacts' LIMIT 10

The first two will work. The third will fail, as you can't select from a
variable.

Alternatively, try this:

SELECT id FROM contacts LIMIT 10
SELECT `id` FROM contacts LIMIT 10
SELECT 'id' FROM contacts LIMIT 10

and all will be even more clear :-)

Mark
--
http://mark.goodge.co.uk
http://www.ratemysupermarket.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2 [at] m.gmane.org