2009-02-12 04:00 阅读 38


I am a new php and mysql programmer. I am handling quite large amount of data, and in future it will grow slowly, thus I am using hash table. I have couple of questions:

  1. Does mysql have hash table built in function? If yes, how to use that?

  2. After couple of days doing research about hash table. I briefly know what hash table is but I just could not understand how to start creating one. I saw a lot of hash table codes over the internet. Most of them, in the first step in to create a hashtable class. Does it mean, they store the hash table value in the temporary table instead of insert into mysql database?

For questions 3,4 & 5, example scenario: User can collect items in the website. I would like to use hash table to insert and retrieve the items that the user collected.

  1. [Important] What are the possible mysql database structure looks like?

    e.g, create items and users table

    in items table have: item_id, item_name, and item_hash_value

    in users table have: user_id, username, item_name, item_hash_value

    I am not sure if the users table is correct?

  2. [Important] What are the steps of creating hash table in php and mysql? (If there is any sample code would be great :))

  3. [Important] How to insert and retrieve data from hash table? I am talking about php and mysql, so I hope the answers can be like: "you can use mysql query i.e SELECT * from blabla..."

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

4条回答 默认 最新

  • 已采纳
    douzhao7014 douzhao7014 2009-02-12 16:40

    (sorry about the italics, underscores can trigger them but I can't find a good way to disable that in the middle of a paragraph. Ignore the italics, I didn't mean to put them there)

    You don't need to worry about using a hashtable with MySQL. If you intend to have a large number of items in memory while you operate on them a hashtable is a good data structure to use since it can find things much faster than a simple list.

    But at the database level, you don't need to worry about the hashtable. Figuring out how to best hold and access records is MySQL's job, so as long as you give it the correct information it will be happy.

    Database Structure

    items table would be: item_id, item_name
    Primary key is item_id
    users table would be: user_id, username
    Primary key is user_id
    user_items table would be: user_id, item_id
    Primary key is the combination of user_id and item_id
    Index on item_id

    Each item gets one (and only one) entry in the items table. Each user gets one (and only one) entry in the users table. When a user selects an item, it goes in the user items table. Example:

    1 | Bob
    2 | Alice
    3 | Robert
    1 | Headphones
    2 | Computer
    3 | Beanie Baby

    So if Bob has selected the headphones and Robert has selected the computer and beanie baby, the user_items table would look like this:

    User_items (user_id, item_id)
    1 | 1    (This shows Bob (user 1) selected headphones (item 1))
    3 | 2    (This shows Robert (user 3) selected a computer (item 2))
    3 | 3    (This shows Robert (user 3) selected a beanie baby (item 3))

    Since the user_id and item_id on the users and items tables are primary keys, MySQL will let you access them very fast, just like a hashmap. On the user_items table having both the user_id and item_id in the primary key means you won't have duplicates and you should be able to get fast access (an index on item_id wouldn't hurt).

    Example Queries

    With this setup, it's really easy to find out what you want to know. Here are some examples:

    Who has selected item 2?

    SELECT users.user_id, users.user_name FROM users, user_items
    WHERE users.user_id = user_items.user_id AND user_items.item_id = 2

    How many things has Robert selected?

    SELECT COUNT(user_items.item_id) FROM user_items, users
    WHERE users.user_id = user_items.user_id AND users.user_name = 'Robert'

    I want a list of each user and what they've selected, ordered by the user name

    SELECT user.user_name, item.item_name FROM users, items, user_items
    WHERE users.user_id = user_items.user_id AND items.item_id = user_items.item_id
    ORDER BY user_name, item_name

    There are many guides to SQL on the internet, such as the W3C's tutorial.

    点赞 评论 复制链接分享
  • doulang2311 doulang2311 2009-02-12 04:16

    1) Hashtables do exist in MySQL but are used to keep internal track of keys on tables. 2) Hashtables work by hashing a data cell to create a number of different keys that separate the data by these keys making it easier to search through. The hashtable is used to find what the key is that should be used to bring up the correct list to search through.

    Example, you have 100 items, searching 100 items in a row takes 10 seconds. If you know that they can be separated by type of item and break it up into 25 items of t-shirts, 25 items of clocks, items rows of watches, and items rows of shoes. Then when you need to find a t-shirt, you can only have to search through the 25 items of t-shirts which then takes 2.5 seconds.

    3) Not sure what your question means, a MySQL database is a binary file that contains all the rows in the database. 4) As in #2 you would need to decide what you want your key to be. 5) #2 you need to know what your key is.

    点赞 评论 复制链接分享
  • doulin8374 doulin8374 2009-02-12 16:43

    If you think a hash table is the right way to store your data, you may want to use a key-value database like CouchDB instead of MySQL. They show you how to get started with PHP.

    点赞 评论 复制链接分享
  • doucuan5365 doucuan5365 2011-12-06 10:20

    I am a new php and mysql programmer. I am handling quite large amount of data, and in future it will grow slowly, thus I am using hash table.

    lookin at your original purpose, use "memcache" instead, it is the most scalable solution while offers the minimal changes in your code, you can scale up the memcache servers as your data go larger and larger.

    点赞 评论 复制链接分享