检索特定行的有效方法...... [英] Efficient ways to retrieve specific rows...
问题描述
首先，请原谅我是一个完整的MySQL新手。
我的网站使用了我自己写的论坛软件（在Perl中），直到
现在，使用了平面文件。这很好用，但是最近我一直想要用b / b
来做更多用户帐户的东西，并且一直在讨论MySQL超过
年。
最后我决定通过将论坛的帐户
系统转换为MySQL数据库（并在我之后转换其余的数据）开始。
舒服）。到目前为止一切正常，我已经想出如何创建表格，插入记录，修改记录等等。
但是我有一个问题，当我通读包含
消息的平面文件时，检索用户信息的最有效方法是什么？
当读取每条消息时，我想找到该用户的相关信息，
将其构建到我的输出中，然后继续下一条消息。现在这是我的
问题。因为我正在阅读一个平面文件的方式，曾经试图以某种方式记忆效率（在非常慢的服务器上回来的时候，尝试
避免数组和哈希）我发现每个消息出现时我都要向MySQL发送单独的
问题。说从1到20很简单
问题来完成打印页面。此外，有时问题可能重复 - 因为我没有将任何结果存储在内存中，如果
用户出现两次，我会问两次。
我是否真的会想要找到一种方法将所有
不同用户整合到一个问题中？或者MySQL的效率是否足够？
这真的不是什么问题？
- Daniel
First, please excuse the fact that I''m a complete MySQL newbie.
My site used forum software that I wrote myself (in Perl) which, up until
now, has used flat files. This worked fine, however lately I''ve been wanting
to do more stuff with user accounts, and had been eying MySQL for over a
year.
Finally I''ve decided to start off small by converting the forum''s account
system to a MySQL database (and convert the rest later after I''m
comfortable). So far everything is working fine, and I''ve figured out how to
create the table, insert records, modify records, and so on.
However I had a question on what was the most efficient way to retrieve
information about a user as I read through the flat file containing
messages.
As each message is read I want to find that user''s relevant information,
build it into my output, then continue on to the next message. Now here''s my
problem. Since I''m reading a flat file in a way that was once trying to be
somewhat memory efficient (on really slow server way back when, trying to
avoid arrays and hashes) I''m finding that I''m having to send separate
questions to MySQL as each message comes up. Say from 1 to 20 very simple
questions to complete printing the page. Also, sometimes the questions might
be repetitive - since I''m not storing any of the results in memory, if a
user appears twice I ask about it twice.
Would I really be better off trying to find a way to consolidate all
distinct users into a single question? Or is MySQL efficient enough that
this isn''t really much of a concern?
- Daniel
推荐答案
2004年8月6日星期五05:18:43 -0400，在mailing.database.mysql中Daniel
Tonks < dtonks @ sunstormADD-DOT-COM>写道：
On Fri, 6 Aug 2004 05:18:43 -0400, in mailing.database.mysql "Daniel
Tonks" <dtonks@sunstormADD-DOT-COM> wrote:
|首先，请原谅我是一个完整的MySQL新手。
|
|我的网站使用了我自己写的论坛软件（在Perl中），直到
|现在，已使用平面文件。这很好，但最近我一直想要
|使用用户帐户做更多的事情，并且一直在讨论MySQL的问题一年。
|
|最后，我决定通过转换论坛的帐户
|来开始系统到MySQL数据库（并在我很舒服后转换其余的数据库）。到目前为止一切正常，我已经想出了如何
|创建表，插入记录，修改记录等。
|
|但是我有一个问题是什么是最有效的检索方式
|我通过包含
|的平面文件阅读有关用户的信息消息。
|
|在阅读每条消息时，我想找到该用户的相关信息，
|将其构建到我的输出中，然后继续下一条消息。现在这里是我的
|问题。因为我正在尝试以某种方式阅读平面文件
|有点内存效率（在非常慢的服务器方式回来时，试图
|避免数组和哈希）我发现我不得不单独发送
|每条消息出现时都会向MySQL提出问题。从1到20说非常简单
|问题，以完成打印页面。此外，有时问题可能是
|重复 - 因为我没有将任何结果存储在内存中，如果是
|用户出现两次我问了两次。
|
|我是否真的会更好地试图找到巩固所有的方法
|将不同的用户分成一个问题？或者MySQL的效率足够
|这不是一个真正的问题吗？
| First, please excuse the fact that I''m a complete MySQL newbie.
|
| My site used forum software that I wrote myself (in Perl) which, up until
| now, has used flat files. This worked fine, however lately I''ve been wanting
| to do more stuff with user accounts, and had been eying MySQL for over a
| year.
|
| Finally I''ve decided to start off small by converting the forum''s account
| system to a MySQL database (and convert the rest later after I''m
| comfortable). So far everything is working fine, and I''ve figured out how to
| create the table, insert records, modify records, and so on.
|
| However I had a question on what was the most efficient way to retrieve
| information about a user as I read through the flat file containing
| messages.
|
| As each message is read I want to find that user''s relevant information,
| build it into my output, then continue on to the next message. Now here''s my
| problem. Since I''m reading a flat file in a way that was once trying to be
| somewhat memory efficient (on really slow server way back when, trying to
| avoid arrays and hashes) I''m finding that I''m having to send separate
| questions to MySQL as each message comes up. Say from 1 to 20 very simple
| questions to complete printing the page. Also, sometimes the questions might
| be repetitive - since I''m not storing any of the results in memory, if a
| user appears twice I ask about it twice.
|
| Would I really be better off trying to find a way to consolidate all
| distinct users into a single question? Or is MySQL efficient enough that
| this isn''t really much of a concern?
你仍然像平面文件一样使用数据库。因此*你*是
仍然在做工作而不是数据库。
如果没看到你的桌子就很难给你一个明确的答案
结构但您可以通过以下查询提取信息：
来自bloggs的SELECT *其中username =''Joe Bloggs'';
对此列表进行排序将是：
SELECT *来自bloggs，其中username =''Joe Bloggs''ORDER BY bloggID;
多个标准（最近30天的Joe Bloggs的条目）
SELECT *来自bloggs，其中username =''Joe Bloggs''和
EntryDate< = CurDate（） - 30;
You are still using the database like a flat file. Therefore *you* are
still doing the work instead of the database.
It is difficult to give you a clear answer without seeing your table
structures but you could extract you information with a query like:
SELECT * from bloggs where username=''Joe Bloggs'';
Sorting this list would be:
SELECT * from bloggs where username=''Joe Bloggs'' ORDER BY bloggID;
Multiple criteria (last 30days of entries by Joe Bloggs)
SELECT * from bloggs where username=''Joe Bloggs'' and
EntryDate<=CurDate()-30;
" Jeff North" < JN **** @ yourpantsbigpond.net.au>在留言中写道
新闻：om ******************************** @ 4ax.com ...
"Jeff North" <jn****@yourpantsbigpond.net.au> wrote in message
news:om********************************@4ax.com...
On Fri，2004年8月6日05:18:43 -0400，在mailing.database.mysqlDaniel
Tonks中< dtonks @ sunstormADD-DOT-COM>写道：
您仍然使用数据库像平面文件。因此*你*仍然在做工作而不是数据库。
如果没有看到你的表格结构很难给你一个明确的答案，但你可以提取你的信息查询如下：
SELECT * from bloggs where username =''Joe Bloggs'';
排序此列表将是：
SELECT * from bloggs where username ='' Joe Bloggs''ORDER BY bloggID;
多个标准（Joe Bloggs最后30天的条目）
来自bloggs的SELECT *，其中username =''Joe Bloggs''和
EntryDate< ; = CurDate（） - 30;
On Fri, 6 Aug 2004 05:18:43 -0400, in mailing.database.mysql "Daniel
Tonks" <dtonks@sunstormADD-DOT-COM> wrote:
You are still using the database like a flat file. Therefore *you* are
still doing the work instead of the database.
It is difficult to give you a clear answer without seeing your table
structures but you could extract you information with a query like:
SELECT * from bloggs where username=''Joe Bloggs'';
Sorting this list would be:
SELECT * from bloggs where username=''Joe Bloggs'' ORDER BY bloggID;
Multiple criteria (last 30days of entries by Joe Bloggs)
SELECT * from bloggs where username=''Joe Bloggs'' and
EntryDate<=CurDate()-30;
这都是真的，但我唯一建立数据库的是帐户
部分。其他一切仍然是平面文件，这就是为什么我仍然有点像这样访问数据库的
。现在......
从平面文件中读取＃1：SUBJECT USERID DATE POST
SELECT stuffneeded FROM accounts WHERE id = userid
流程&打印消息。
从平面文件中读取＃2：SUBJECT USERID DATE POST
来自帐户的SELECT stuffneeded WHERE id = userid
过程&打印消息。
唯一可以解决的问题就是将整个线程读入内存，
收集所需用户ID的列表，获取来自
数据库的所需信息，然后打印出所有内容。除非MySQL可以处理这种重复简单请求的
，在这种情况下，我宁愿不把精力放到
完全重写可能需要的问题几个月后重写了几美元。
我意识到一旦我制作了所有索引，这将成为一个问题
和消息的一部分数据库（我正在阅读的书给了一些好的啊
ha！我想做的事情的例子），但是成为一个完全新手这样的
数据库我只是从一个方面开始很小。然后我会开始
合并其他功能。
到目前为止，我对编码的效率非常满意......是
40行处理和更新平面文件之前只需要一个
的情侣！哇哇！
- Daniel
That''s all true, but the only thing I''ve MADE a database is the accounts
section. Everything else is still flat file, which is why I''m still sort of
accessing the database that way. Right now...
Read #1 from flat file: SUBJECT USERID DATE POST
SELECT stuffneeded FROM accounts WHERE id=userid
Process & print message.
Read #2 from flat file: SUBJECT USERID DATE POST
SELECT stuffneeded FROM accounts WHERE id=userid
Process & print message.
The only way around that seems to be to read the entire thread into memory,
gather a list of needed userid''s, get the needed information from the
database, and THEN print everything out. Unless MySQL can handle this sort
of repeated simple request, in which case I''d rather not put the effort into
completely rewriting a problem that will probably need to be rewritten in a
few months.
I realize that this will become less of an issue once I make all the indexes
and messages part of a database (the books I''m reading give some good "ah
ha!" examples for stuff I''d like to do), but being an utter newbie to such
databases I''m starting small with just one aspect of it. Then I''ll start
merging other features.
So far I''m very happy with how efficient the coding is for this... what was
40 lines to process and update the flat file before is down to just a
couple! Woo hoo!
- Daniel
Daniel Tonks写道：
Daniel Tonks wrote:
除非MySQL可以处理这种类型重复的简单请求，在这种情况下，我宁愿不努力完全重写一个可能需要在几个月内重写的问题。
Unless MySQL can handle this sort
of repeated simple request, in which case I''d rather not put the effort into
completely rewriting a problem that will probably need to be rewritten in a
few months.
MySQL可以处理它。像你描述的BB系统可能无法操作足够数量的数据来压倒DBMS，除非
有数百个并发用户。
但是如果你想做一些改进，你可以做以下任何一项：
- 使用参数化查询获取用户信息
准备一次，例如SELECT stuffneeded FROM accounts WHERE id =？每次将userid作为
参数传递时，每次执行准备好的查询。
- 获取每个用户信息给用户ID一次，并保持在会话期间缓存在内存中的
。将其存储在由用户ID索引的散列
数组中。我认为这种类型的信息通常是静态的，并且在给定用户的会话期间不可能改变。
- 做你所说的关于将线程加载到内存中以找到与线程相关的用户ID的完整列表，然后加载它们：
" SELECT stuffneeded FROM accounts
WHERE id IN（
MySQL can probably handle it. A BB system like you describe isn''t
likely to operate on enough volume of data to overwhelm a DBMS, unless
there are many hundreds of simultaneous users.
But if you want to do some improvements, you could do any of the following:
- Fetch the user information using a parameterized query that you
prepare once, e.g. "SELECT stuffneeded FROM accounts WHERE id=?" and
execute the prepared query each time while passing the userid as a
parameter.
- Fetch the user information for each given userid once, and keep it
cached in memory for the duration of the session. Store it in a hashed
array indexed by the user id. I assume this type of information is
usually static, and won''t be likely to change during a given user''s session.
- Do what you said about loading the thread into memory to find the
complete list of user id''s relevant to the thread, and then load them:
"SELECT stuffneeded FROM accounts
WHERE id IN (
这篇关于检索特定行的有效方法......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
