0

I just added another index to a table in my database but now the site is crashing, queries get locked and not unlocked and queries are not executed (I can see them in the process list and they usually take less than a second to execute but now they are at around 600 seconds and more etc.).

I didn't change anything on my site, just added an index. How is this possible, did anybody had similiar issues before that might help me solve my issue?

Update: I'm not really sure what happened, but one specific query was really slow after adding the index (even though this index wasn't needed/used for this specific query). Even removing the index didn't help to speed the query up again. After that I removed all indizes from this table and added the indizes again one by one, after doing that the query was fast again. Not sure if this helps anybody but this fixed it for me.

2 Answers 2

4

Find the query in question say it is something like

select * from tableName where user='bob' order by ts;

Then add EXPLAIN in front

EXPLAIN select * from tableName where user='bob' order by ts;

From there you can see if the index is in use and how many tables it has to scan and how many it is returning. My guess is this.. You had an index it was using before and now you created this new index which is worse and it is using that one instead somehow.

3

You've not provided any details of the database schema, the index you added, the database engine in use, the queries being run, whether the change in performance affects all queries / queries using the new index / queries using the modified table / some queries only. So it's rather hard to say.

The only reasons for adding an index are:

1) to speed things up

2) to force a unique constraint

(again, you didn't say what your index is intended to do).

Since obviously it has failed to speed things up, then you should drop the index and see if it normal service resumes - but you might want to look at how you gather and analyse your stats before you do that.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .