StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I would recommend this:</p> <p>Use intergers instead of chars/varchars. this way you index faster (except the referrer). Also, I can recommend to get summary tables. Although it's not really normalized but the query will be executed instantly - specially if you have a big organization with lots of traffic.</p> <p>So here's the tables:</p> <pre><code>create table statistics ( browser tinyint(3) UNSIGNED not null default 0, version float(4,2) not null default 0, ip INT(10) UNSIGNED not null default 0, createdon datetime, referrer varchar(5000), key browserdate (browser, createdon), key ipdate (ip, createdon), // etc.. ); </code></pre> <p>browser 0 = unknow, 1 = firefox etc.. This can be done in your code (so you load the same code for inserting and selecting). i dont use enum here because if you need to alter the table and you have millions of records this can be painful. new browser = new number in the code which is way faster to change.</p> <p>this table can be used to resummarized all the other tables if something happens. so you create an index for the inline summary table (example browser)</p> <p>Now the summary table:</p> <pre><code>create table statistics_browser_2011_11 ( browser tinyint(3) UNSIGNED not null default 0, version float(4,2) not null default 0, number bigint(20) not null default 0, createdon datetime, unique key browserinfo (createdon, browser, version) ); // browsers stats for november 2011 </code></pre> <p>This way when you inserts (you get the date of the user when he accessed the site and create a $string that match with the table name) into this table you only have to use the <code>on duplicate key number = number +1</code>. this way when you retrieve the browser statistics is super fast.</p> <p>now here you will have to create a merge table because if you are the second of the month and you want to query the last 7 days, you will need the current month and the last month table. here's more info: <a href="http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html">http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html</a></p> <p>and you repeat the process for the other information: ip, referrer etc...</p> <p>in order to maintain these tables, you will have to create a cronjob that creates tables for the next month. simple PHP script that gets the current year/month and then create the table for the next month if it does not exists and then merge them)</p> <p>this might be a little of work but this is how i do it at work (with similar data) with 12 terabytes of data and 5,000 employees that fetch the databases. my average load time for each query is approx 0.60 seconds per requests.</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