What's more efficient?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

What's more efficient?

Post by JellyFish »

What's more efficient. I'm making this rating system. And I can go about this two ways.

The first is that I have two fields: rating and num_rates. In the rating I'll store a number that grows every time someone rates and num_rates would hold the number of people that rated. For example, if someone rates, let's say, 5 stars 5 is added to rating and 1 is added to num_rates.

Then all I need to do is divide rating by num_rate and I'd get the average.

The second way would be having only one field: rating. But this time storing an array of rates. For example if someone rates 5, it will not add to rating, but instead concatenate a new item in a list of comma separated number that range from 1 to 5.

Which method is better? Which would you use? The first idea requires two fields but might have less space. The second requires only one field but the array might get out of hand.

Which is more efficient then the other?

Thanks for reading, and merry Christmas!
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I would personally store each rating as it's own row and the date of which it was rated. Databases are designed to handle many rows easily assuming you index them properly, so I wouldn't be worried about performance until it actually becomes a problem.

I would choose flexibility over performance 99.99% of the time, plus you can implement those cool rating features such as, 19%+ since last week like imdb.com does
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post by JellyFish »

Jcart wrote:I would personally store each rating as it's own row and the date of which it was rated. Databases are designed to handle many rows easily assuming you index them properly, so I wouldn't be worried about performance until it actually becomes a problem.

I would choose flexibility over performance 99.99% of the time, plus you can implement those cool rating features such as, 19%+ since last week like imdb.com does
How would creating another table for ratings work? An example would be much appreciated.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Example:

Code: Select all

id       | item_id         | date                  | rating
------------------------------------------------------------------------------------
1        | 1               | 2007-12-25 12:11:13   | 5
2        | 1               | 2007-12-25 12:12:13   | 2
3        | 1               | 2007-12-25 12:13:13   | 1
4        | 1               | 2007-12-25 12:14:13   | 3
5        | 1               | 2007-12-25 12:15:13   | 3.5
6        | 1               | 2007-12-25 12:16:13   | 4

Code: Select all

SELECT AVG(`rating`) FROM `ratings` WHERE `item_id` = 1
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post by JellyFish »

Okay. If you had to pick only between my first two options, what would it be?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Neither, but if someone put a gun to my head the first.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

It's OK Jcart - I'd be watching your back. No one should have to suffer the horrors of Database Denormalization in this day and age.

@JellyFish: The ratings table is worth doing. As an added bonus, the time it takes you to set it up is likely less than the time it would take to implement either of your earlier suggestions (queries and all). Give it a shot - we're here to help debug!

As a side note, MySQL is much faster at doing joins, etc... than PHP is of simulating them. MUCH faster.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Kieran Huggins wrote:It's OK Jcart - I'd be watching your back. No one should have to suffer the horrors of Database Denormalization in this day and age.
Most Hilarious Torontonian of the Year++
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post by JellyFish »

I think I'm going to need some good mySQL references on joined tables.

What benefit will a separate table give me, opposed to having a two extra fields in a current table?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

It will be faster to program, it will perform better, and it will be easier to visualize when programming. It's not as scary as it sounds!
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post by JellyFish »

I haven't built a rating system of any kind yet.

So before I do, I must say I realize that I have implemented my keyword/tagging system all wrong.

I have a table like so:

username
name
etc...
keywords

and like Kieran said using php to loop through each one of them is ridiculous. I should separate it into two tables:

table1:
username
name
etc...

and

table2
username
name
keywords

and then select all from table one where username and name equal to the table2 username and name or something like that.

Could someone give some great SQL tutorials or good documentation? I find that mysql.com to be little confusing.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

almost!

users:
id
username
name
etc...

tags:
id
tagname

tags_users: (the join table)
id
tag_id
user_id

A tag can have many users, and a user can have many tags. Also, you can change the tag's name (spelling, etc...) or add a context column without killing your other tables.

Ratings are a many (ratings) to one (user) relationship. A user has many ratings, but a rating was only made by one user.

ratings:
id
user_id
rating
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post by JellyFish »

Kieran Huggins wrote:almost!

users:
id
username
name
etc...

tags:
id
tagname

tags_users: (the join table)
id
tag_id
user_id

A tag can have many users, and a user can have many tags. Also, you can change the tag's name (spelling, etc...) or add a context column without killing your other tables.

Ratings are a many (ratings) to one (user) relationship. A user has many ratings, but a rating was only made by one user.

ratings:
id
user_id
rating
Well, it's not really a tag system for users, but a tag system for information created by a user. When I wrote:

username
name
etc...

It may appear like a user table, and I realize that now. But the 'etc...' part is more fields such as heading and description. Username is really a field that specifies who the "article"(I'll call them) belongs to. And name isn't a first name or last name or both, but instead the name of the "article".

I guess virtually it would make no difference weather it was a table for users or articles. But just in case, I thought I'd might clear that up.

Now with your example, Kieran, how would I select all the records in users where...I haven't go a real clue on where to start? :?

Could I maybe get an example on how I would select rows from users with the tags from a php string: $_GET['search']?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

To select all the tags for a user (untested):

Code: Select all

SELECT * FROM tags INNER JOIN tags_users ON tags.id = tags_users.tag_id WHERE (tags_users.user_id = $user_id )
To select all users with a specific tag (also untested):

Code: Select all

$tag_list = implode(',',$tag_array);

Code: Select all

SELECT * FROM users INNER JOIN tags_users ON users.id = tags_users.user_id WHERE (tags_users.tag_id = tag.id AND tag.name IN $tag_list)
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Post by JellyFish »

Kieran Huggins wrote:To select all the tags for a user (untested):

Code: Select all

SELECT * FROM tags INNER JOIN tags_users ON tags.id = tags_users.tag_id WHERE (tags_users.user_id = $user_id )
To select all users with a specific tag (also untested):

Code: Select all

$tag_list = implode(',',$tag_array);

Code: Select all

SELECT * FROM users INNER JOIN tags_users ON users.id = tags_users.user_id WHERE (tags_users.tag_id = tag.id AND tag.name IN $tag_list)
What does the ON clause do? Is it just like the WHERE clause?
Post Reply