Whats best?

[johnrb87]

Hi

 

I am redoing a friends website and he has a log of files accessed held in a SQL database.

 

At the moment he has 550,000 records, but it's becoming a bit slow when queries are run.

 

Would it therefore be better to have multiple tables of data, most likely stamped by month

 

log_june2010

log_july2010

log_august2010

 

and so on, and just tie them over with JOINS, or is it better to tweak the current table and optimise it a little and let it continue to fill up?

 

thanks very much

[premiso]

The first thing that pops into my mind is what columns are queried and are they indexed?

 

If not, I would index the columns that the database is queried on (excluding the Primary Key as that is indexed by default). You should not need to separate them out into different tables.

[johnrb87]

oh ok, so what is the max number of rows a database table can handle?

 

It might not be indexed as there is likely to be 10 times more data being processed, so it could be a non indexed table with 2+ million records.

 

Indexing the database every second might be too much eh?

[premiso]

Databases were made for large amounts of data. To optimize it, you should index only rows you will query. If you plan on having a search you should look into Full Text searching or another searching type option such as Sphynx. But splitting the data into two separate tables should not be done as you would have to query the second table as well and there in you now have two queries you have to run and it will tax the database just the same, if not harder.

[awjudd]

You don't need to keep re-indexing tables.  They will maintain their indices and add the new records with indices as needed.

 

~judda

[johnrb87]

ok, but surely as the number of rows in the database increase, the slower the database would become because it's having to do a query on a table holding tons of data?

 

So if having multiple tables that spread the data is not a good idea, then how do I handle the tables becoming very slow as they get bigger?

 

Data might be added in very often, so surely if I index it every time some new data is added, the whole system will slow down even more?

[DavidAM]

ok, but surely as the number of rows in the database increase, the slower the database would become because it's having to do a query on a table holding tons of data?

That's why databases were invented: to handle large amounts of data. They are designed to do queries on tables holding "tons" of data.

 

So if having multiple tables that spread the data is not a good idea, then how do I handle the tables becoming very slow as they get bigger?

as premiso said: "index the columns that the database is queried on". An index provides a pointer to the rows that contain the indexed data, this way the database server can go directly to the indexed data instead of having to read thousands of rows.

 

Data might be added in very often, so surely if I index it every time some new data is added, the whole system will slow down even more?

as juddster said: "You don't need to keep re-indexing tables"  You create the index ONCE then whenever data is added to (or removed from) the table, the database server updates the indexes.

 

The database servers have tools to evaluate a query so you can see where indexes need to be created.  See EXPLAIN PLAN

[eran]

Don't index blindly. Understand how indexes work and how you should apply them.

I suggest going over this excellent presentation on indexing strategies - http://www.scribd.com/doc/2376115/Coding-and-Indexing-Strategies-for-Optimal-Performance