Simple questions about resources management

[soltek]

Hello, I have two questions about good practice, I guess.

 

1) SELECT * FROM TABLE demands more resources that SELECT id FROM TABLE?

Imagine the table has 7 columns.

 

2) Imagine (there's no heaven...) I have a module on my website, in Ajax that, if the user has new private messages, it opens a pop-up. For this the Ajax would check from X to X seconds if there are new messages using:

SELECT COUNT(check) FROM messages WHERE checked =yes and recipient=$CURRENTUSER;

This would got to look intro thousands of entries, right?

 

I had an idea to make this lighter. When someones sends a private message to another user, there will be an update:

UPDATE FROM users SET new_message = new_message + 1 WHERE id=$RECEIPIENT_ID;

This way the ajax refresh would only have to look for one entry.

 

Is this a good idea? Bad idea? Or totally worthless?

[fugix]

1) no it does not demand anymore resources than that.

2) yes it would look at multiple rows

3) your update query makes sense to me

[PFMaBiSmAd]

1) Selecting all the columns will use more resources than selecting one column, due to the amount of memory used to hold the data and the processing time needed to transfer that data. (Hopefully, you are not storing images or complete books in your database table as that would cause a huge jump in the resources used if you are selecting the columns with that data in it when you don't need it.)

 

2) If there is no change in the table data, repeatedly querying with the same query simply returns the result that was previously returned by that query -

 

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

 

The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.

 

The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

 

3) You can store the 'last message id' somewhere (database, session variable...) so that you will only need to query for rows that have a message id greater than the last one your code processed.

[soltek]

3) You can store the 'last message id' somewhere (database, session variable...) so that you will only need to query for rows that have a message id greater than the last one your code processed.

 

If I got it right, that would store only the last message that was sent. In that case, only one user would have the 'new message warning', even if 2 seconds before someone had sent messages for other users.

[PFMaBiSmAd]

You are trying to retrieve and display information for a specific user. You would keep track of the last message that was processed (displayed) for each user.