Database Optimization: Saving Time and CPU Usage Essay

924 Words4 Pages
Database optimization is something, that while may go unnoticed, is very important a very important for saving time and CPU usage. In attempting queries on the math database downloaded form stackexchange. A query is an expression written in a programming language, in this case SQL, that is used for data look up in a database. Stackexchange is a large compressed file that contains a record of all the forums from the website stackoverflow.com. I specifically used the math files since they are one of the largest files in size and serve as a perfect example for why database optimization, and finding efficient SQL queries is important. I have noticed that complicated queries can be optimized to make for faster, more complete results. With the…show more content…
( Query Optimization in Database Systems) To sum it up query optimization is important so that these costs can be reduced as much as possible even as the amount of data increases. Using the schema: users id integer, name text, rep, integer, about text) posts( id integer, title text, owner_id text, post_id, created text) com(comment_id integer, post_id integer, comment_text text, user_id integer), I ran a query “SELECT COUNT(post_id), created FROM posts GROUP BY created” to get a count of how many posts were made and on what days. This took anywhere from five to ten seconds to run which is a pretty good amount of time. The problem with the query is that it is too simple. Every time it is run it is searching the entire database and printing everything out. For one thing this is not ideal since it is not very readable and the information is not very specific. To make this query better I added on “WHERE” clauses to give it a specific target. If looking for a how many posts where created on '2010-03-27 14:53:20.727' then run query “SELECT count(post_id) FROM posts WHERE created = '2010-03-27 14:53:20.727'. While that seems simplistic for the topic, it is worth noting since a big part of optimizing queries is knowing what you want and specifically
Open Document