I have a website with a database of 2,4 million entries. Now I want to use mod_rewrite to produce clear URLs like example.com/this-is-a-beautiful-url. To recognize which URL belongs to which page I want to build a URL-table which refers to the website's content. Like "this-url-example" belongs to the post with the ID 123. Now 2 friends are telling me 2 different ways to build this table and I don't know which one is the best.
我有一个拥有2400万条目数据库的网站。现在我想使用mod_rewrite来生成清晰的url，比如example.com/thisis -a-beautiful-url。为了识别哪个URL属于哪个页面，我想构建一个URL表，它引用网站的内容。就像“this-url-example”属于ID为123的post。现在有两个朋友告诉我用两种不同的方法做这个桌子，我不知道哪一种是最好的。
- Advice: "Make a MD5 hash of the URLs and save them in a binary field with an index".
- 建议:“对url进行MD5哈希，并将其保存到带有索引的二进制字段中”。
- Advice: "Simply save the urls in plain text and put an index on it".
- 建议:“只需将url保存为纯文本并在其上添加索引”。
Which method would provide faster select-queries? Do you have another advice? Thanks!
哪种方法可以提供更快的选择查询?你还有别的建议吗?谢谢!
3 个解决方案
#1
2
Go for a compromise, have urls as sitename.com/12345/clean-url-format
妥协一下，把url设为sitename.com/1234.5 /clean-url格式
You have both keywords in a readable URL and the identified which you can use to fetch the row by the primary key for speed.
在可读的URL中，您有两个关键字，以及您可以用来通过主键获取行的速度。
It would be reasonable to redirect urls without the text part to the canonical version with it.
在没有文本部分的情况下，将url重定向到规范版本，这是合理的。
#2
2
MD5 Hash was designed for data comparison purposes. I.e. checking that a password is valid or that a file remains unchanged.
MD5散列是为数据比较而设计的。例如，检查密码是否有效或文件是否保持不变。
If you plan on being able to change and modify the URL (for search engine optimization purposes) I would stick to Plain text.
如果您打算修改和修改URL(用于搜索引擎优化目的)，我将坚持使用纯文本。
Also if you want to create Mod Rewrite Commands (in your HTACCESS) file - it will need to be plain text to enable you to link that URL with a particular page.
另外，如果您想要创建Mod重写命令(在您的HTACCESS)文件中——它需要是纯文本，以便您能够将该URL与特定的页面链接起来。
#3
0
I would not use either mysql or md5 hashing. Instead, use a key-value store such as dbm or leveldb. If you are using Apache's mod_rewrite, it has a feature, RewriteMap that can read the keys and values directly from a dbm db, or by calling an external program. The type can be sdbm, gdbm, ndbm or db (Berkeley DB), and apache includes a program, httxt2dbm, to create the database. If you do want to use MySQL, RewriteMap can do that as well with the dbd option, and mod_dbd. Performance-wise, using a hash index on MySQL should be similar to Berkley DB.
我不会使用mysql或md5哈希。相反，可以使用像dbm或leveldb这样的键值存储。如果您正在使用Apache的mod_rewrite，它有一个特性RewriteMap，可以直接从dbm db或调用外部程序读取键和值。类型可以是sdbm、gdbm、ndbm或db (Berkeley db)， apache包括一个程序，httxt2dbm，以创建数据库。如果您确实想使用MySQL, RewriteMap也可以使用dbd选项和mod_dbd。在性能方面，在MySQL上使用哈希索引应该类似于Berkley DB。
#1
2
Go for a compromise, have urls as sitename.com/12345/clean-url-format
妥协一下，把url设为sitename.com/1234.5 /clean-url格式
You have both keywords in a readable URL and the identified which you can use to fetch the row by the primary key for speed.
在可读的URL中，您有两个关键字，以及您可以用来通过主键获取行的速度。
It would be reasonable to redirect urls without the text part to the canonical version with it.
在没有文本部分的情况下，将url重定向到规范版本，这是合理的。
#2
2
MD5 Hash was designed for data comparison purposes. I.e. checking that a password is valid or that a file remains unchanged.
MD5散列是为数据比较而设计的。例如，检查密码是否有效或文件是否保持不变。
If you plan on being able to change and modify the URL (for search engine optimization purposes) I would stick to Plain text.
如果您打算修改和修改URL(用于搜索引擎优化目的)，我将坚持使用纯文本。
Also if you want to create Mod Rewrite Commands (in your HTACCESS) file - it will need to be plain text to enable you to link that URL with a particular page.
另外，如果您想要创建Mod重写命令(在您的HTACCESS)文件中——它需要是纯文本，以便您能够将该URL与特定的页面链接起来。
#3
0
I would not use either mysql or md5 hashing. Instead, use a key-value store such as dbm or leveldb. If you are using Apache's mod_rewrite, it has a feature, RewriteMap that can read the keys and values directly from a dbm db, or by calling an external program. The type can be sdbm, gdbm, ndbm or db (Berkeley DB), and apache includes a program, httxt2dbm, to create the database. If you do want to use MySQL, RewriteMap can do that as well with the dbd option, and mod_dbd. Performance-wise, using a hash index on MySQL should be similar to Berkley DB.
我不会使用mysql或md5哈希。相反，可以使用像dbm或leveldb这样的键值存储。如果您正在使用Apache的mod_rewrite，它有一个特性RewriteMap，可以直接从dbm db或调用外部程序读取键和值。类型可以是sdbm、gdbm、ndbm或db (Berkeley db)， apache包括一个程序，httxt2dbm，以创建数据库。如果您确实想使用MySQL, RewriteMap也可以使用dbd选项和mod_dbd。在性能方面，在MySQL上使用哈希索引应该类似于Berkley DB。