Codeigniter sessions class w/ database storage option not optimized?

Question

I use codeigniter's session class with the option to store session data in a database. This is an example of the select query that runs for every user request to retrieve a session:

SELECT *
FROM (`ci_sessions`)
WHERE `session_id` = 'f7fd61f08a229kdu3093130a3da17e14'
AND `user_agent` = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6; rv:5.'

Here is the table structure for session data as defined in the user guide:

CREATE TABLE IF NOT EXISTS  `ci_sessions` (
session_id varchar(40) DEFAULT '0' NOT NULL,
ip_address varchar(16) DEFAULT '0' NOT NULL,
user_agent varchar(50) NOT NULL,
last_activity int(10) unsigned DEFAULT 0 NOT NULL,
user_data text DEFAULT '' NOT NULL,
PRIMARY KEY (session_id)
);

It's my understanding that whenever you have a query that's intended to return a single result it's good practice to use LIMIT 0, 1 so that when the database engine finds the desired row it simply returns rather than continuing to scan the entire table for more matches. Therefore would it be more efficient for this query to be written as:

SELECT *
FROM (`ci_sessions`)
WHERE `session_id` = 'f7fd61f08a229kdu3093130a3da17e14'
AND `user_agent` = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6; rv:5.'
LIMIT 0, 1

Is there any reason it isn't already written this way?

if you have more than one matching row, that's a big negative in the first place ;) This will atleast throw a fatal error, if your code is designed to expect single row — Stewie, Aug 02 '11 at 14:41
I am interested to see what SO says about the LIMIT, I personally don't do that because I throw fatals if there is more than 1. — afuzzyllama, Aug 02 '11 at 14:42
The idea is that there shouldn't be more than one matching row however. Sessions are supposed to be unique to each user? And I might end up with a sessions table that contains ten's of thousands of sessions that is queried for every page load, any performance boost here might be noticible everywhere. — Casey Flynn, Aug 02 '11 at 14:57
Curious about this myself. After looking at the Session class source, I see no reason not to use the LIMIT clause. They use `row()` to grab the data too, which returns only the *first* row... — Wesley Murch, Aug 02 '11 at 16:28
I'm not sure what exactly you are asking but I always use `LIMIT` when retrieving 1 row. It's good practice. — CyberJunkie, Aug 05 '11 at 15:50
@CyberJunkie: That is the essence of the question: Why doesn't CI already do this? Is there a reason, and what is that reason? — Wesley Murch, Aug 05 '11 at 18:46
@Casey: Indeed, this doesn't seem to be optimized, even if CI uses cursor fetching. Also you don't need to use the offset parameter if only fetching one (and unique) row, `LIMIT 1` would do it just fine and should only be faster (not sure if it's actually more efficient, but it would make sense). — Alix Axel, Aug 05 '11 at 19:04
Even educated speculation on why the LIMIT is not used in this case would be welcome. I see nothing in the CI Core or Reactor bitbucket issues regarding this, so it may be something to bring to them. Such a simple one line optimization would be welcome if there is indeed no reason *not* to use it. — Wesley Murch, Aug 05 '11 at 19:21
Now, I am certainly not well educated in SQL or database performance in general (read: noob), but does the fact that `session_id` is a primary key have anything to do with `LIMIT 1` not being an optimization? — Wesley Murch, Aug 05 '11 at 19:39
@CyberJunkie: Who says it's good practice to always use `LIMIT`? — hakre, Aug 06 '11 at 18:42

score 6 · Accepted Answer · edited May 23 '17 at 11:53

6

There could be only one row, matching the user_agent and the session_id, so there will be no need to limit the number of selections, it is already limited by being the only one.

The issue has been reported to the Codeigniter Reactor devs on Bitbucket and dismissed as invalid:

https://bitbucket.org/ellislab/codeigniter-reactor/issue/422/session-class-should-use-limit-1-when

Their response:

the session_id field is a primary key so it's going to be a unique row

Is there any point using MySQL "LIMIT 1" when querying on indexed/unique field?

So it would appear that this is not in fact an optimization, it's just unnecessary.

edited May 23 '17 at 11:53

Community

1
1

answered Aug 05 '11 at 19:05

Grigor

4,139
10
41
79

2

Incorrect/unclear answer: without the LIMIT, the entire table will be searched for values matching the WHERE clause. There could be thousands of records, so this impedes performance. There *should* be only one row, and even if there *are* more for some reason, CI only *returns* the first one. – Wesley Murch Aug 05 '11 at 19:14
make a binary search function and you will avoid getting into searchnig all records. Linear search is not very fast. I don't know why you worry about it so much, you won't probably feel any difference anyways. But yeah, if you really want to change things around, make a bineary search through SQL. – Grigor Aug 05 '11 at 19:22
@Grigor, see the first response to this question: http://stackoverflow.com/questions/1787275/can-a-binary-search-be-done-in-a-database-in-sql – Casey Flynn Aug 05 '11 at 23:15
And the reason this could be a performance issue is that this query is executed for every single page request each user initiates. A site with potentially many thousands of user sessions that are active at any given time will see compounding performance loss for the extra wasted time the DBMS spends searching the rest of the sessions table after it's already found the session it's looking for – Casey Flynn Aug 05 '11 at 23:17
1

Looks like you were correct Grigor, I reported it to the dev team and they said it was unnecessary and cited another Stack Overflow post. I'm feeling more comfortable with this, thanks for chiming in - This is resolved as far as I'm concerned. Apologies for the arrogant tone in my first comment. I would have liked to hear a better explanation than a one-liner though. I still have to wait at least 14 hours to award the bounty, so feel free to add one so the answer can be self contained. – Wesley Murch Aug 06 '11 at 04:28
Sorry for commenting late, I just logged in to stackoverflow. I am glad I helped you somehow. I used to use codeigniter for a year, so I got to know things about it. – Grigor Aug 08 '11 at 06:35

Codeigniter sessions class w/ database storage option not optimized?

1 Answers1