[SOLVED] Adding an index to all tables in all DB's in one command?

[brashquido]

Hi All,

 

I manage a VPS for myself and a couple of friends, and it seems that a lot of the PHP apps we use don't use indexes at all. MySQL uses a lot of CPU time, which I'm told can come back to the lack of having/using indexes.

 

1) Can I add indexes to all tables in all the DB's on my server at once, and if so how?

 

2) Will just adding indexes and nothing else improve MySQL performance, or do the queries in the PHP code have to be modified to use the new index? 

[artacus]

1) No

 

2) You have to be smart about your indexing. Indexing will slow your inserts and updates, take up more space but improve your lookups. It is especially important that you index foreign keys, columns you will be joining tables on, and columns you will be doing a lot of searching or filtering on.

[bcamp1973]

I'm in the process of learning how to index tables as well.  In it's most basic form it's a fairly simple topic to grasp. However (from my perspective - that of a novice) it gets complex after that.  Here's a couple articles that i've been trying to absorb that might help you...

 

http://www.databasejournal.com/features/mysql/article.php/1382791

 

http://hackmysql.com/case4

 

For basic queries like this...SELECT * FROM my_table WHERE size_id=xx My impression is that a simple index on my_table.size_id is adequate.

 

For simple joins like this...

SELECT

   foo.id,

   foo.bar_id,

   bar.bla,

   bar.yada

FROM

   foo

LEFT JOIN

   bar

ON

   foo.bar_id = bar.id

AND

   bar.bla = 'xxx'

ORDER BY

   bar.yada

ASC

 

My impression is indexes on foo.bar_id, bar.id (already indexed if it's a primary key), bar.yada and bar.bla are required.

 

artacus, you've helped me before and your skills are beyond mine so please feel free to correct me if I'm off base.  I'm just trying to absorb what i'm reading and regurgitate a little of it

[artacus]

bar.yada and bar.bla are required.

 

No indexes are required per say.

 

Here foo.id and bar.id are both pk's so they are already indexed. (should be)

foo.bar_id is a fk so you definitely SHOULD index it.

bar.bla would be indexed if it is being searched regularly

bar.yada I  wouldn't index.

[brashquido]

This is probably a stupid question, but what is the easiest way to find which primary keys and foreign keys are not indexed? I imagine there are no shortcuts here, and it would involve enabling the slow query log with the log-queries-not-using-indexes switch and then examining the log?

[artacus]

You have phpmyadmin or some other tool right? Just look at the structure of your tables, it will tell you what is indexed.

[brashquido]

So obvious, thanks