CODEWITHSUNDEEP
mysqlsqloptimizationindexing
JohnT
JohnT

Reputation: 967

Should I create an Index on my table?

I've a table with 7 columns, I've on primary on first column, another index (foreign key).

My app does:

SELECT `comment_vote`.`ip`, `comment_vote`.`comment_id`, COUNT(*) AS `nb` FROM `comment_vote` 
SELECT `comment_vote`.`type` FROM `comment_vote` WHERE (comment_id = 123) AND (ip = "127.0.0.1")

Is it worth to add an index on ip column? it is often used in my select query.

By the way is there anything I can do to quick up those queries? Sometimes it tooks a long time and lock the table preventing other queries to run.

Upvotes: 0

Views: 348

Answers (4)

grokster
grokster

Reputation: 6277

YES! Almost always add an INDEX or two or three! (multi-column indexes?) to every column.

If it is in not a WHERE clause today, you can bet it will be tomorrow. Most data is WORM (written once read many times) so making the read most effective is where you will get the most value. And, as many have pointed out, the argument about having to maintain the index during a write is just plain silly.

Upvotes: -1

duffymo
duffymo

Reputation: 308743

Yes, do create an index on ip if you're using it in other queries.

This one uses column id and ip, so I'd create an index on the combination. An index on ip alone won't help that query.

Upvotes: 0

Henning
Henning

Reputation: 16311

A good rule of thumb: If a column appears in the WHERE clause, there should be an index for it. If a query is slow, there's a good chance an index could help, particularly one that contains all fields in the WHERE clause.

In MySQL, you can use the EXPLAIN keyword to see an approximate query plan for your query, including indexes used. This should help you find out where your queries spend their time.

Upvotes: 1

isobar
isobar

Reputation: 1175

If you are searching by ip quite often then yes you can create an index. However your insert/updates might take a bit longer due to this. Not sure how your data is structured but if the data collection is by ip then may be you can consider partitioning it by ip.

Upvotes: 1

Related Questions