0

Does the USE INDEX() have any effect here?

SELECT `posts`.`id` FROM posts USE INDEX() where `posts`.`size` > 1024;

(I have it presently in some SQL because of the way the query is programmatically constructed depending on certain criteria.)

2

I've just did a test, and apparently it forces the query not to use any index:

mysql> explain select email from B2 where length(email)>30;
+----+-------------+-------+-------+---------------+-------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | B2    | index | NULL          | email | 67      | NULL | 118097 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select email from B2 use index() where length(email)>30;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | B2    | ALL  | NULL          | NULL | NULL    | NULL | 118097 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

So, you SHOULD remove this clause if it is empty.

On another note, I think you should not force an index, with the exception of few rare cases. If the you notice that a query is using an index which is not optimal, you'd better analyze the situation, and know why is this happening, and fix it. (Some times, optimize would do the job)

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.