Questions about Php and Mysql Hash Table

Question

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..."

Note: [Important] = urgent questions, need the answers.

Thanks in advanced for answering my questions. I need you guys help!!

Answer 1

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.

Answer 2

(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:

Users:

1 | Bob
2 | Alice
3 | Robert

Items

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.

Answer 3

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.