Normalized ratings/reviews?

Post by **exp** » Thu Sep 11, 2003 9:43 am

Elberet wrote:Not to mention the technical aspect... :P Unless a normalized vote is stored permanently as is, a query to determine the average vote would have to also find all votes for all animes for all users who voted for the anime, or something like that. I don't think SQL can do those calculations in a single query...

har har,

you can do almost anything within a single query, but it would kill the db.
don't think you ppl are willing to wait 5mins for an anime page to display bc the votes are being checked to generate the overall vote }:o)

BYe!
EXP

Elberet · Post by **Elberet** » Thu Sep 11, 2003 1:09 pm

exp wrote:you can do almost anything within a single query, but it would kill the db.

Only for very narrow definitions of "almost", tho.

Post by **PetriW** » Thu Sep 11, 2003 3:00 pm

Only for very narrow definitions of "almost", tho.

With some creative joins, unions, groupings etc you can do A LOT in one query and get a VERY heavy one too.

My definition "one query" here is of course that all those subqueries are part of the one main query.

Elberet · Post by **Elberet** » Thu Sep 11, 2003 5:43 pm

I know what intelligent JOINs and GROUP BYs can do, but UNIONs and INTERSECTs don't count as single queries since the database actually has to perform to SELECTs and compare the results. Same goes for sub-selects.

You might send one SQL statement to the DB, but it's still more then one query.

Post by **PetriW** » Thu Sep 11, 2003 6:41 pm

Now don't joins spawn subqueries?

What is a subquery really, does it have to be a separate SELECT statement? I most certainly don't think so.

With SIMPLE queries you'll get nowhere and implying that a "query" is only simple queries is kinda weird.

Post by **exp** » Thu Sep 11, 2003 9:39 pm

well, i was talking about everything which can be packaged into one sql command (joins, groupings, subselects, ...)
you can do quite a lot with ONE such command, the db has to do multiple selects of course.

BYe!
EXP

Elberet · Post by **Elberet** » Fri Sep 12, 2003 1:41 am

PetriW wrote:Now don't joins spawn subqueries?

Nope, not always. A simple example: "SELECT * FROM a LEFT JOIN b ON a.primary_key = b.foreign_key;". If a.primary_key is in fact the PRIMARY key in that table and b.foreign_key has an INDEX, then the SQL optimizer will very efficiently match the rows since it can use the indexes to directly find the rows it needs. If there's no index, it depends on how the database is written: It might create an index in memory to cover all rows in the ON statement, or it might do a straight join between the tables and merge the ON and WHERE statements, or...

Post by **PetriW** » Fri Sep 12, 2003 6:01 am

Well, it has to do a select on the index....

Elberet · Post by **Elberet** » Fri Sep 12, 2003 9:15 am

Nope, indexes are different.

Post by **PetriW** » Fri Sep 12, 2003 1:15 pm

Well, you still have to query it!

Elberet · Post by **Elberet** » Sat Sep 13, 2003 3:00 am

Nope. A (simplified) query is the process that involves opening the database file, finding the correct place to start reading, copying the data into memory and discarding those datasets that do not match the condition. Even tho the word index doesn't appear there, the index is used while the DB looks for the correct place to start reading. If multiple tables are joined in a query, it is true that the database has to read all of the tables, but it's still only one query because it reads all those tables only once. UNIONs and sub selects are different tho, since it's not possible to combine those two queries into a single file I/O process.

Oh man, things brings up baaad memories back from when I had to implement an ISAM database in Java. Major yuck...

Post by **PetriW** » Sat Sep 13, 2003 11:39 am

Hmm, lucky me I don't really have to know any of this anymore!