1

I am having some data in the session table, which holds the user location along with some other data related to user

The session table consists of multiple columns like

  • id - PRIMARY KEY AUTO-INCREMENT
  • session_id - UNIQUE KEY(This will be unique for every time, even if user logs in multiple times, this will be generated UNIQUE)
  • user_id (This user id is unique to every user)
  • user_location (Holds the coordinates of the user)

Problem Statment

I wanted to know that is there any performance difference between the below two queries.

First Query

SELECT user_location FROM session WHERE session_id = 'some-session-id-goes-here';


Second Query

SELECT user_location FROM session WHERE session_id = 'some-session-id-goes-here' AND user_id ='usome-user-id';

Both the queries give correct data, but is there any effect on performance if suppose there is millions for data present in this session table.

Running both queries on smaller set of data < 4000 did not give any difference in fetching time.

Not A Bot
  • 2,474
  • 2
  • 16
  • 33
  • 1
    As user-id is not a key and session-id is unique key these 2 query will not have any performance differences. You can also check this using `explain` – MD Ruhul Amin Feb 19 '20 at 04:40
  • @ruhul okay, I will try this. – Not A Bot Feb 19 '20 at 04:42
  • If `session_id` was the primary key (assuming its not null too) rather than a unique key then the performance would be the same and probably better than both queries currently. Is `id` needed? – danblack Feb 19 '20 at 04:46
  • ``session_id`` is not **NULL**, it will be some ``UNIQUE`` value every time. Can you tell how can a ``PRIMARY KEY`` would have been better than ``UNIQUE`` in SELECT operation? @danblack And ``id`` is not much need, just for normal auto-increment – Not A Bot Feb 19 '20 at 04:50
  • If `id` referenced in another table, or you code, its probably not needed. An AI does make it easy to insert at the end of a table rather than rebalancing as non-sequential PKs are added. This [doc](https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html) has a bit on primary vs secondary keys. Basically a secondary key ends implicitly with the primary key id, fetching via a secondary key need to go back to the primary (clustered) key to retrieve fields like `user_location` that isn't in the index. With `session_id` as a PK, its already found. Added insert penalty possibly however. – danblack Feb 19 '20 at 05:07
  • Compare the `EXPLAIN {query}` aspects for different table and queries. Should make the forecasted higher volume structures/queries more reliable. – danblack Feb 19 '20 at 05:09
  • @danblack thanks for the explanation. I will try with ```EXPLAIN``` – Not A Bot Feb 19 '20 at 05:23
  • It depends on the index definitions – Strawberry Feb 19 '20 at 07:59

1 Answers1

1

Normally, when you declare a column with UNIQUE Key it is indexed by default. As you have not mentioned if your user_id is declared unique or not(you said user_id is unique, I assume its values are but not defined in your create table script). So in that sense they will give same performance. But if you index your user_id too then the case can be different. When you have multiple column indexed and you are querying using them, you must maintain their index-sequence after where clause and your query performance will be better than the current one. Otherwise, the effect will be negative for not maintaining the sequence order. You can check columns index sequence by

show index from your_table_name

For some more insight, you can check here MySQL: unique field needs to be an index?

user404
  • 1,934
  • 1
  • 16
  • 32
  • ``user_id`` is not **UNIQUE** in this ``session`` table, but it is **UNIQUE** in some other table called ``users``. I don't think that will matter for this table ``session``. – Not A Bot Feb 19 '20 at 04:58
  • you are right, that won't matter as you have not declared it for this table. – user404 Feb 19 '20 at 05:00
  • So in the current scenario, there will be no considerable difference between the two queries I have written. Also by any chance to make this fetching more faster or this is the optimum solution? – Not A Bot Feb 19 '20 at 05:04
  • I think so. Right now I can't see any better solution other than indexing the column. – user404 Feb 19 '20 at 05:06