“喜欢”的良好替代/实践使用 PostgreSQL 和 PHP？
我正在使用一个 Postgres 数据库，我无法控制它的管理。我正在构建一个日历，用于查看资源（物理项目）在特定日期是否在线或离线。不幸的是，如果他们离线，我只能通过在文本字段中查找资源名称来确认这一点。
我一直在使用
select * from log WHERE log_text LIKE 'Resource Kit 06%'
问题是，当我们使用 180 多次（每天至少 6 个资源）构建日历时，速度非常慢。有谁知道加快速度的方法（请记住我无法修改数据库）。另外，如果我在数据库端无能为力，那么在php端我可以做些什么吗？
I'm working with a Postgres database that I have no control over the administration of. I'm building a calendar that deals with seeing if resources (physical items) were online or offline on a specific day. Unfortunately, if they're offline I can only confirm this by finding the resource name in a text field.
I've been using
select * from log WHERE log_text LIKE 'Resource Kit 06%'
The problem is that when we're building a calendar using LIKE 180+ times (at least 6 resources per day) is slow as can be. Does anybody know of a way to speed this up (keep in mind I can't modify the database). Also, if there's nothing I can do on the database end, is there anything I can do on the php end?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为，为此需要某种形式的缓存。由于您无法更改数据库中的任何内容，因此您唯一的机会就是从中提取数据并将其以更易于访问和更快的形式存储。这高度依赖于插入表中的数据的频率。如果插入的数量多于选择的数量，那么它可能不会有太大帮助。其他方式有很小的机会提高性能。
也许您可以考虑使用Lucene搜索引擎，它具有全文索引功能。 Zend 有实现，甚至 Apache 也有一些 http 服务。不过我没有机会测试它。
如果您不使用那么强大的东西，您可以在 php 中编写自己的缓存机制。它不会像 postgres 那么快，但可能比没有索引的 LIKE 查询更快。如果您的查询需要更复杂（条件、分组、排序...），您可以使用 SQLite 数据库，它是基于文件的，不需要在服务器上运行额外的服务。
另一种方法是在数据库中使用触发器，它可以以更多索引的方式将数据存储所需的信息插入到其他表中。但如果没有管理数据库的权限，这可能是死路一条。
如果您需要更具体的信息，请更具体地回答您的问题。
I think, that some form of cache will be required for this. As you cannot change anything in database, your only chance is to pull data from it and store it in some more accessible and faster form. This is highly dependent on frequency of data inserted into table. If there are more inserts than selects, it will not probably help much. Other way there is slight chance of improved performance.
Maybe you can consider using Lucene search engine, which is capable of fulltext indexing. There is implementation from Zend and even Apache has some http service. I haven't opportunity to test it however.
If you don't use something that robust, you can write your own caching mechanism in php. It will not be as fast as postgres, but probably faster than not indexed LIKE queries. If your queries need to be more sofisticated (conditions, grouping, ordering...), you can use SQLite database, which is file based and doesn't need extra service running on server.
Another way could be using triggers in database, which could on insert data store required information to some other table in more indexed manner. But without rights to administer database, it is probably dead end.
Please be more specific with your question, if you want more specific information.