-1

Are there significant benefits between the two SQL select ?

SELECT * FROM table USE INDEX (cod_clie) WHERE cod_clie = "example" AND pwd_clie = "example" LIMIT 1;

SELECT * FROM table WHERE cod_clie = "example" AND pwd_clie = "example" LIMIT 1;

cod_clie is an INDEX field, of course!

Frits
  • 7,341
  • 10
  • 42
  • 60
Elia Marotta
  • 13
  • 1
  • 5
  • Indexing your query do improve performance, but identifying columns you wish to display also helps. So try to avoid using "*" as much as possible. – hungrykoala Jun 08 '17 at 08:36
  • 1
    As above, not really. LIMIT without ORDER BY is fairly meaningless. 'SELECT *' is evil. And it's generally best to let MySQL figure out which indexes to use. – Strawberry Jun 08 '17 at 08:38
  • Possible duplicate of [What is an index in SQL?](https://stackoverflow.com/questions/2955459/what-is-an-index-in-sql) – Twinfriends Jun 08 '17 at 08:43
  • @Strawberry thanks for your reply. If I've figured out the best query in my case is : SELECT field1, field2 FROM table USE INDEX (cod_clie) WHERE cod_clie = "example" AND pwd_clie = "example" LIMIT 1 ORDER BY id DESC – Elia Marotta Jun 08 '17 at 08:45

4 Answers4

0

Usually not, since mysql should use the index. To be 100% sure just append explain to your queries and compare the output.

EXPLAIN SELECT * FROM table USE INDEX (cod_clie) WHERE cod_clie = 'example' AND pwd_clie = 'example' LIMIT 1;
EXPLAIN SELECT * FROM table WHERE cod_clie = 'example' AND pwd_clie = 'example' LIMIT 1;
rainerhahnekamp
  • 1,087
  • 12
  • 27
0

By default query analyzer find best index to improve query performance. But due to many manual indexing or heavily modified tables (INSERT or DELETE for example), over time the key distributions (Cardinality) diverge from the true state.

If an Optimizer that picks the wrong plan can cause severe performance issues. This means that your server can hit a performance problem at the most unexpected time.

Lucky for us, we can force the Optimizer to pick a particular plan (index) with the 'USE INDEX' hint.

Virendra Jadeja
  • 821
  • 1
  • 10
  • 20
  • It should be pointed out that maybe the optimiser has a good reason to choose what you think is an inefficient plan. If you fail to properly understand the performance implications of a particular plan, and force the wrong index, you can in fact make performance worse. – Disillusioned Mar 12 '18 at 14:36
-1

The index is here to speed up your SQL search. https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

If you don't have any index, SQL will go trough the whole table when you're looking for a specific value (WHERE cod_clie = "example").

If you have big databases with a lot of data you'll realize a quite big difference in performance time.

Twinfriends
  • 1,972
  • 1
  • 14
  • 34
  • Presumably the index exists, otherwise the query that uses it wouldn't run at all. So your answer just seems to regurgitate a classroom lesson doesn't really answer the actual question in any way. – Disillusioned Mar 12 '18 at 14:31
-1

SQL indexes enhance performance of SQL select query through arranging values of indexed field in specific sort order which thereby minimizes number of rows in a table to be scanned until matching row is found which contains filter value in that column.

Usually indexes are applied on fields used as filters in SQL select query.

Rubin Porwal
  • 3,736
  • 1
  • 23
  • 26
  • The question isn't about the benefits of indexes, so a text-book-like quote doesn't seem useful. You can improve your answer by making sure you answer the actual question. – Disillusioned Mar 12 '18 at 14:34