I have a huge table, but I know in most cases, only a small portion of the data are used for a query. Is there a way to make MySQL only lookup this small portion? Does "view" help in this case?
3
-
1No a view would likely not help performance. Try using where clauses and proper indexing. And anything less than terrabyte sized tables are not huge!HLGEM– HLGEM2012-03-13 20:24:30 +00:00Commented Mar 13, 2012 at 20:24
-
There are 170 million records, but only 2 million records are often used. It takes about 10 seconds for a query that aggregate these 2 million records. Is there a way to optimize that?DrXCheng– DrXCheng2012-03-13 20:27:56 +00:00Commented Mar 13, 2012 at 20:27
-
1WEll I can't say without seeing the query and the table structures and the indexes and the explain plan.HLGEM– HLGEM2012-03-13 20:29:07 +00:00Commented Mar 13, 2012 at 20:29
Add a comment
|
2 Answers
Simply read this article - http://dev.mysql.com/doc/refman/5.0/en/optimization.html
Optimize indexes, statements/clauses, caching and server itself.
Comments
Many columns
If you have many columns, be sure to only name the used columns in the SELECT statement. This allows MySQL to skip over the unused columns, not returning values that you won't be using anyway.
So, instead of the following query:
SELECT *
FROM users
Use this type of query:
SELECT id, last_name, first_name
FROM users
Many rows
If you have many rows, add indexes to the columns that you are filtering on using the WHERE clause. For example:
SELECT id, last_name, first_name
FROM users
WHERE last_name = 'Smith'
The above query selects specific columns for all user records where the last name is 'Smith'.
If you have an index on the last_name column, MySQL would be able to locate the records that match the criteria in your WHERE clause very quickly.
