Is it correct to index all columns in a mysql database?

Question

My site is taking too long to load the contents and the CPU load used by mysql service reaches sometimes 200%. Checking the tables realized that the tables all columns were used as index. This is correct? It can affect performance?

My server configuration: 16GB RAM, 3.4GHz Most tables have about 25k ~ 50k lines. And 10~20 Columns.

All tables indexing are like this example. All columns as indexes

Answer 1

No, you don't index all columns. You index columns that are specifically involved in a WHERE clause, and sometimes if they're involved in an ORDER BY.

In this case you'd want an index on type:

SELECT name FROM users WHERE type='admin'

In this case you'd want an index on active,type:

SELECT name FROM users WHERE type='admin' AND active=1

In this case you might want an index on active,type,name:

SELECT name FROM users WHERE type-='admin' AND active=1 ORDER BY name LIMIT 10

The more indexes you add the slower writes will be but the faster reads will be. This is a classic trade-off. Evaluate carefully what indexes you need and apply them only if there will be a tangible benefit. Don't just slap them on because you feel like they should be there.

On super tiny tables, those with <1000 rows, indexes won't help that much because a table scan won't take that long. On anything non-trivial they're absolutely essential.

If you're having performance problems I'd suggest that your schema is the biggest obstacle, not the lack of indexes.

Answer 2

No, you should not index all columns. It will be slower when you write data. It is not normal that your CPU reaches sometimes 200%. I suggest you check your SQL.

I'm sure optimizing SELECT statements is helpful.

http://dev.mysql.com/doc/refman/5.6/en/statement-optimization.html

1.Optimize your query with cache. Try to use parameter instead of functions.

(Thanks for @tadman's suggestion)

// no cache
$sql = "SELECT username FROM user WHERE signup_date >= CURDATE()";

// cache
$today = date("Y-m-d");
$sql = "SELECT username FROM user WHERE signup_date >= '$today'"); 

2.EXPLAIN you SELECT wisely. It helps you find potential performance issues.

EXPLAIN select name,phone from user where name="JakLiao";

3. Use "LIMIT N" when you only need first one row result.

SELECT * FROM user WHERE country = 'China' limit 1;

4. Use index when you need it in where clause.

//row "name" should add index.
select * from user where name LIKE 'Jak%' limit 10;

5. Use index when you join tables.

select company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = 123;

6. Avoid "select *"

//not suggest
SELECT * FROM user WHERE user_id = 1;
//suggest
SELECT username FROM user WHERE user_id = 1;

7. PROCEDURE ANALYSE(), MYSQL will help you analysis your database and data. 8. COUNT(1) when you want to know rows of a table.

//not suggest
SELECT count(*) FROM user;
//suggest
SELECT count(1) FROM user;