2

For example, i have a table (users) and its column is name

Case 1:

I know all "name"s values are different (unique) but i not used 'primary key' for this column

Case 2:

I know all "name"s values are different (unique) and i used 'primary key' for this column

And if i use both this querys for "Case 1" and "Case 2"

1. SELECT * FROM `users` WHERE `name` = 'Obama'

2. SELECT * FROM `users` WHERE `name` = 'Obama' LIMIT 1

Than what performance difference will be for Case 1 and Case 2?

MarsPeople
  • 1,772
  • 18
  • 30
  • 1
    As of with mysql (with its not-so-smart optimizer) there will be a theoretical difference. In practice you hardly can measure it. – zerkms Feb 10 '15 at 03:42
  • "I know all "name"s values are different" --- so the index is not marked as `UNIQUE`? – zerkms Feb 10 '15 at 03:44
  • possible duplicate of [Is there any point using MySQL "LIMIT 1" when querying on indexed/unique field?](http://stackoverflow.com/questions/3848390/is-there-any-point-using-mysql-limit-1-when-querying-on-indexed-unique-field) – PM 77-1 Feb 10 '15 at 03:45
  • @PM77-1 the checked answer there is unfortunately very poor (the others are even worse) – zerkms Feb 10 '15 at 03:46
  • What about http://stackoverflow.com/questions/1774361/is-limit-1-recommended-for-query-where-where-condition-is-based-on-pk then? – PM 77-1 Feb 10 '15 at 03:48
  • @PM77-1 it (the checked answer) also addresses `EXPLAIN` as its main argument which is just the wrong tool to answer this question. To clarify why it's wrong: 1. It does not show number of index seeks/comparisons. The `rows` indicates how many rows were returned, not checked. 2. It does not take into account `LIMIT` node *at all*. – zerkms Feb 10 '15 at 03:49
  • @zerkms i just "know" it but not used "index" or marked "UNIQUE" – MarsPeople Feb 10 '15 at 03:52
  • @Kumul so it's not covered by index? – zerkms Feb 10 '15 at 03:53
  • yes (my english is poor sory if i not understand) – MarsPeople Feb 10 '15 at 03:54
  • And you know how indexes work and what they are for? The question is a nonsense then: I cannot realize someone is worried about performance then they intentionally perform a fullscan over a table. – zerkms Feb 10 '15 at 03:55
  • for example: i have 10 million rows in my 'users' table, and i am not used 'primary key' for its column, then if i run 2 queries(queries is in quetion) Is not difference in performance? – MarsPeople Feb 10 '15 at 03:58
  • If you have 10M rows and perform a fullscan intentionally in production - your DB access must be revoked. "Is not difference in performance?" --- it's a wrong question. The right question would be: why haven't you created an index? – zerkms Feb 10 '15 at 03:59
  • But you are not understand the question, question is not "how can i write best query for my select?", question is "what difference in this 2 queries on 2 cases?" so i want to learn if i use 'LIMIT' then is sql will behave like 'break;' ? or not? – MarsPeople Feb 10 '15 at 04:04
  • It does not matter. To get a well performing database it should be both: proper schema and proper queries. For this question the only correct answer is: create index for the column. – zerkms Feb 10 '15 at 04:04

1 Answers1

3

selecting just 1 result will always be faster than selecting all, whether indexed or not, because LIMIT 1 stops after the first match (think of the case where you have millions of matches. But even with just 2 matches, it is faster to send back 1 result than 2.)

Having an index on the column will typically (but not always) be faster than not having an index, because with an index the mathching rows can be found in O(log n) time, while without an index it takes a linear scan and O(n) time. The exception is when most of the rows match and the simple (and fast) linear scan can find the matches in less time than the overhead of the binary search on the index + main table read.

Edit: if the query is run for an existence test, yes, definitely LIMIT 1. It would be slow and wasteful to compute a million-record result set just to check whether it's empty. (There is also a mysql EXISTS keyword that's very similar, slightly different syntax)

Andras
  • 2,995
  • 11
  • 17
  • 'LIMIT 1 stops after the first match' and ''if the query is run for an existence test, yes, definitely LIMIT 1" this sentences are exactly what I wanted to hear. – MarsPeople Feb 10 '15 at 04:14
  • If the query is run for an existence test, then use `EXISTS ( SELECT * ... )` _without_ a LIMIT -- that "semi-join" has extra optimizations. – Rick James Feb 19 '15 at 01:10
  • Don't bother to use LIMIT 1 if you know there is at most one row. Any imagined savings will be miniscule. Having the clause implies that you think there could be more than one row. – Rick James Feb 19 '15 at 01:11