need mysql table suggestions

[Stanley]

so here's the deal:
right now whenever the page is loaded a SELECT query is run to grab the data fields and an UPDATE query is run to update the view counter (views=views+1)

- any suggestions how I can optimize this?

For instance I noticed that YouTube doesn't update the view counter whenever the page is loaded, instead the views are cached and the count is updated every few hours. How does this work?

I should add that it's a MyISAM table type, so it's optimized for read queries.

 

[Icecube]

you can use the LOW_PRIORITY modifier
here's the link to the docs, MySQL AB :: MySQL 5.0 Reference Manual :: 13.2.10 UPDATE Syntax
the update statement is executed only when there's nobody running SELECT queries on the table
sorry but I can't help you more than this since I have never used it

 

[Stanley]

cool, thanks for the suggestion.

 

[ahtum]

What about using inserts instead of update ( sort of a cache table ). And from time to time counting the inserts, update the views and delete the inserts. Never tested but i think it would be faster than update on every view.

 

[Stanley]

maybe I outta do an INSERT into an InnoDB table, and then cron job update the main table every 6 hours or whatnot

 

[Icecube]

you might consider MySQL AB :: MySQL 5.0 Reference Manual :: 13.2.4.2 INSERT DELAYED Syntax but in my opinion toying with another table just to update a counter isn't worth

maybe I outta do an INSERT into an InnoDB table, and then cron job update the main table every 6 hours or whatnot

this would create a lot of queries to insert in the secondary db, read from it and the insert into the main one
I think the first solution (low priority) would be faster because it runs when nobody is using the db

 

[Stanley]

I think the first solution (low priority) would be faster because it runs when nobody is using the db

so the LOW_PRIORITY flag will keep the quries in a queue until the database is available?

I'm afraid that one of the pages will get Dugg and then I'll have 10,000 update queries stuck in a queue

 

[ahtum]

" If you use the LOW_PRIORITY keyword, execution of the UPDATE is delayed until no other clients are reading from the table. This affects only storage engines that use only table-level locking (MyISAM, MEMORY, MERGE)."

So with 10k queries i think ur getting the server going cucu.

 

[Icecube]

so the LOW_PRIORITY flag will keep the quries in a queue until the database is available?

I'm afraid that one of the pages will get Dugg and then I'll have 10,000 update queries stuck in a queue

do you think having to run 2 queries for each visit to store the value in the other database would be better?
what you said about being dugg might be a problem
you could create a table with page_id and page_views(or whatever your fields are) with page_id referencing page_id in the main table and run updates on that secondary table (rather than inserts)
then as you said set a cron job that reads the value and runs an update on the main table, without LOW_PRIORITY

not sure if this makes sense

I think differences under standard load would be unnoticeable, under heavy load having 2 tables (one for SELECT queries and the other for UPDATE) might be better
I think you should run the cron job more often than 6 hours, a counter that doesn't update in 6 hours is pointless

 

[Stanley]

Is an InnoDB truly faster than a MyISAM for writes?

I think I'll do two separate tables, because then I have the additional benefit of being able to track the top 10 most popular items of the day.

 

[Icecube]

Is an InnoDB truly faster than a MyISAM for writes?
...

I don't know
the advantage I see in 2 tables is that the table you write to isn't locked by a select query, I didn't consider which of the 2 is faster

 

[chatmasta]

Store the data in a flat file and then run a cron job to update from that every half an hour or whatever.

 

[Stanley]

now what would be the most efficient way to run this update query?
are there any drawbacks to running the query on the same page?
for instance, I can run it within a 1x1 invisible iFrame at the bottom of the page..
any suggestions?

 

[Stanley]

Store the data in a flat file and then run a cron job to update from that every half an hour or whatever.

you really think it'd be faster than a database?
they're both just files, stored on the hard drive

 

[a.titus1]

If you really want to avoid hitting the database with an update for every page load, you can look into using memcache (if youre using php).

Awhile back I read about someone who used memcache (or something similar) for ip delivery for a high volume website - instead of reading the ips from a database or flat file, they just had them stored in memory so there was no look up. So if you just cache the page views and only update every 2 hours or whatever, you should be able to handle high volume spikes (a la digg).