我使用 codeigniter 的会话类，并可以选择将会话数据存储在数据库中。这是为每个用户请求检索会话而运行的选择查询的示例：
SELECT *
FROM (`ci_sessions`)
WHERE `session_id` = 'f7fd61f08a229kdu3093130a3da17e14'
AND `user_agent` = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6; rv:5.'
以下是 用户指南：
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)
);
据我了解，每当您有一个打算返回单个结果的查询时，最好使用 LIMIT 0, 1，以便数据库引擎找到所需的行它只是返回而不是继续扫描整个表以查找更多匹配项。因此，将此查询编写为：
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
是否有任何原因没有这样编写？
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?
发布评论
评论（1）
可能只有一行与 user_agent 和 session_id 匹配，因此不需要限制选择的数量，它已经被限制为唯一的。
该问题已报告给 Bitbucket 上的 Codeigniter Reactor 开发人员，并被视为无效而被驳回：
https://bitbucket.org/ellislab/codeigniter-reactor/issue/422/session-class-should-use-limit-1-when
他们的回应：
所以看起来这实际上并不是一种优化，它只是不必要的。
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:
So it would appear that this is not in fact an optimization, it's just unnecessary.