CODEWITHSUNDEEP
mysql
DrXCheng
DrXCheng

Reputation: 4122

Optimizing MySQL database query

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?

Upvotes: 1

Views: 65

Answers (2)

Ami
Ami

Reputation: 1254

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.

Upvotes: 0

Otar
Otar

Reputation: 2591

Simply read this article - http://dev.mysql.com/doc/refman/5.0/en/optimization.html

Optimize indexes, statements/clauses, caching and server itself.

Upvotes: 3

Related Questions