Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Kind of a generic question; but I have noticed that large tables in certain CMS's can slow them down to a crawl. The solutions for this I've seen are to just truncate those tables.

Why do large tables slow down SQL queries so much?

How do Indexes help large tables?

How does disk speed factor into this?

share|improve this question

migrated from stackoverflow.com Aug 24 '13 at 18:48

This question came from our site for professional and enthusiast programmers.

1  
No-answer: Usually because the people that wrote the software and/or designed the database didn't do a good job. Obviously database engines today can cope with very large databases (obviously as in: use google and you should find ample evidence of multi-terrabyte databases being used) so "large databases" doesn't slow down all software systems. –  Lasse V. Karlsen Aug 24 '13 at 17:44
1  
In most database implementations a table is represented by one or more files. If you don't know at precisely what position a piece of information is in a file then you have to read through the whole file and find that piece of information. An index is a smaller file, not containing all the information in the table file that tells us where a certain piece of information is in the table file. It all boils down to file sizes and obviously disk read speeds. The more entries in a table, the larger the file, the longer time it takes to find something specific in it. –  Sergiu Paraschiv Aug 24 '13 at 17:44
3  
"Large tables" don't slow down a query. A select using the PK value in a table with 100 rows is nearly as fast as the same in a table with 100 million rows. Queries only get slower if they need to process more rows. Summing 100 values will always be faster than summing 100 million values. The problem is not the large table, the problem are queries processing too many rows, or processing them inefficiently. –  a_horse_with_no_name Aug 24 '13 at 19:36
add comment

1 Answer

up vote 2 down vote accepted

Most of the time, sql performance issues comes from a poor miss job on the indexing. Selecting the wrong field to index or even the lack of clustered index can create huge bottle necks. I have seen many, many times that most dba forgets to create indexes for deletes. Yeah. I know, a delete... How come? Deletes, at least in sql server, performs several tasks that involve checking if the element can be deleted, check related tables, etc and those tasks can be slow as molasses without the proper indexing.

On the other hand, performance issues can very well come from lazy programmers who don't really care about performance or creating heaps on the server. These type of guys just creates select queries or select * without caring if these queries are performing full table scans, and believe me, in those situations, it doesn't matter if the db is 3Mb or 1Gb.

Finally, if you are really planning to have very large tables, you should consider a good partition policy and create your db with that in mind. This way, you can create the proper partitioned indexes.

As in most cases, in databases, the problem is between the keyboard and the chair ;)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.