3

I have a query that is running very slowly. The table is was querying has about 100k records and no indexes on most of the columns used in the where clause. I just added indexes on those columns but the query hasn't gotten any faster.

I think this is because when a column is indexed, it's value is written in the index at the time of insertion. I just added the indexes now after all those records were added. So is there a way to "re-run the indexes" on the table?

Edit

Here is the query and explain result:

enter image description here

Oddly enough when I copy the query and run in directly in my SQL manager tool it runs quite fast so may bye the problem is in my application code and not in the query itself.

Improve this question
4
  • Run explain select ... to see what the engine does. Commented Aug 12, 2012 at 20:16
  • 1
    The hypothesis in your second paragraph is not true. The problem is elsewhere. Commented Aug 12, 2012 at 20:17
  • 1
    When you create an index, it immediately populates itself with the relevant data - you're almost certainly barking up the wrong tree. As Juergen d says - please post the EXPLAIN and someone will be able to help. Commented Aug 12, 2012 at 20:18
  • It does not matter when you add indexes. On every select they will be applied. Commented Aug 12, 2012 at 20:18

2 Answers 2

Reset to default
3

Mysql keeps consistent indexes. It does not matter if the data is added first, the index is added first, or the data is changed at any time. The same final index will result (assuming the same final data and index type).

Your slow query is not caused by adding the index later. There will be some other reason.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

This is an extremely common problem.

Use MySQL explain http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

When you precede a SELECT statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order.

Using these results... verify the index you created is functioning the way you expected.

If not, you will want to tweak your index until you have it working as expected.

You might want to create a new table, create indexes, then insert all elements from old table to new while testing this. It's easier than dropping and re-adding indices a million times.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.