-1

I've been wonder how link tracking websites store all data! Okay let say i've 1000 page in my database

----------------
| ID  |  Pages |
----------------
|  1  | page1  |
----------------
|  2  | page2  |
----------------

And i'm willing to record IP,browser name and operation system of each visitor for each link so i decided to add database table data like

-----------------------------------------------------
| PageID  |      IP         |  browser  |  system   |
-----------------------------------------------------
|    1   |  000.000.000.00  |  firefox  |   win7    |
-----------------------------------------------------
|    2   |  000.000.000.01  |    IE     | android   |
-----------------------------------------------------
|    2   |  000.000.000.02  |   Chrome  | kardishan |
-----------------------------------------------------

but what if i got 10,000 visitors for each page!

Say each page will gets about 10,000 visitors, so i think database might breaks as it means new 30,000 lines will be added! also if i wanted for example to query about how many visitors has android system for this simple query might not even respond

now think about 1 million visitors per each page means 3 millions lines !!

so do anyone has better idea of explain to me if there is another route i can follow for doing this without cross my database border

thank you

Reham Fahmy
  • 1,058
  • 1
  • 7
  • 10
  • 5
    A DB has no problem with millions of records – juergen d May 30 '16 at 13:02
  • 2
    That's nothing, every DB can easily handle the number. When querying adding an index might not be wrong depending on the query. – Daan May 30 '16 at 13:03
  • @juergend thank you for your reply, but i'm worry about delay time in querying! i try it before and after 2 millions of data record my website stopped responding and hosting support said i used too much of resources – Reham Fahmy May 30 '16 at 13:04
  • 3
    As long as you have appropriate indexing, millions of records is not an issue for a database; the key is appropriate indexing for your queries – Mark Baker May 30 '16 at 13:04
  • Could you show us the query you're trying to execute on the table, so we can help you create an index? – Daan May 30 '16 at 13:06
  • @Daan thank you, but what do you mean of "appropriate indexing"! my query was to get the sum of visitors who has android system `SELECT SUM(column_name) FROM table_name` – Reham Fahmy May 30 '16 at 13:13
  • 2
    I think you're looking for a `COUNT(*)` and to get the number of visitors you have to add the `WHERE system = 'android'`. As for the index: `ALTER TABLE tablename ADD INDEX myindex (\`system\`);`. This will not get unique visitors!. – Daan May 30 '16 at 13:19
  • @Daan thank you so much, i never knew about index before .. i will start searching for explain/tutorial .. you given me the light – Reham Fahmy May 30 '16 at 13:24
  • i've found this question almost same of what i want to ask about [How do I add indexes to MySQL tables?](http://stackoverflow.com/questions/3002605/how-do-i-add-indexes-to-mysql-tables) – Reham Fahmy May 30 '16 at 13:27
  • @Daan Now i got confused! _Indexing your database can drastically decrease the loading time of your web applications_ some vote up for indexing and some vote down .. is it right or wrong to do indexing if you have database table with X million of rows!! – Reham Fahmy May 30 '16 at 14:22

2 Answers2

0

You can add one more column named count. Then You must save only unique visitors per day. All non-unique visits should increment count column.

Pyton
  • 1,291
  • 8
  • 19
0

You should be telling the DBMS that certain column combinations only occur once in your tables. If you were doing that, MySQL would be defining indexes automatically. Ie via PRIMARY KEY and UNIQUE NOT NULL constraints.

philipxy
  • 14,867
  • 6
  • 39
  • 83