StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIs this a practical concept for an activity log?
text
Body
copied!<p>I'd like to create an "activity log" for my PHP site, using one InnoDB MySQL table. Anyone who visits the website will insert a new record into the table, which will record...</p> <ul> <li>Their IP address</li> <li>The ID of the account they are signed into (or null, if not signed in)</li> <li>The request they made to the server</li> <li>The date and time the request was received</li> <li>The user-agent sent, but <strong>only if</strong> a PHP script determines it to be a bot (otherwise null)</li> </ul> <p>At the same time, I can use the table to...</p> <ul> <li>Determine the amount of hits each page receives per year/month/day/etc</li> <li>Determine the amount of unique visitors per year/month/day/etc</li> <li><strong>If practical,</strong> retrieve the previous information on-the-fly with a PHP script, filtering out bot requests if I'd like to</li> </ul> <p>I have some questions (and some reasoning) for the following table I have in mind:</p> <pre><code>CREATE TABLE `activity` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `ip` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `account` int(11) unsigned DEFAULT NULL, `request` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `time` time NOT NULL, `year` year(4) NOT NULL, `month` tinyint(2) unsigned NOT NULL, `day` tinyint(2) unsigned NOT NULL, `bot` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) </code></pre> <ol> <li><p>Is this style of logging practical? Practical meaning inserts and selects can be performed in milliseconds. I realize <em>plenty</em> of records can be generated this way, but I'm not entirely certain if there's a better way to accomplish everything I want to do.</p></li> <li><p>Moreover, would it be practical to SELECT the number of hits "today" and place it at the bottom of each page served? I'm relatively new to working with larger databases, and I'm still learning what queries would be quick and what queries would be painful.</p></li> <li><p>Should I keep the primary key? I can use it to go back and edit any row I want on a whim (which I really can't see myself doing for an important reason), but will it significantly slow down my INSERTs? Are there any advantages? On the same note, I shouldn't add any more indices for the same reason -- right? Including foreign keys (for the account column)?</p></li> <li><p>Is my alternative of capturing the current date and time practical? I first started this table with a single DATETIME column, but read somewhere that tables like this might benefit from columns dividing the date information; If I want to count the number of hits "today," for example, I could limit my results to...</p> <pre><code>WHERE year="2012" AND month="02" AND day="16" </code></pre> <p>...in contrast to...</p> <pre><code>WHERE date > "2012-02-15 23:59:59" </code></pre></li> </ol> <p>Thanks in advance!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload