1
vote
3answers
128 views

MySQL high cpu load slow quries being logged need help to understand

I'm trying to troubleshoot a high load problem on a server, Today for some reason MySQL is taking up excessive CPU time. I have enabled slow-queries and found this query and others similar. The ...
0
votes
2answers
62 views

Added an index to my MySQL database, now queries get locked

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 ...
0
votes
1answer
209 views

In MySQL 5.1 InnoDB, does the maximum length of a VARCHAR affect secondary index size? [closed]

Assuming the data is the same either way, does the maximum length of the VARCHAR affect the space usage of a secondary index? Does InnoDB use fixed length records for indexes? Assume that we're ...
2
votes
2answers
817 views

MySql crash: Incorrect key file for table

We have a MySql 5.5 database running InnoDB on Linux. One table (named table_search) is running MyISAM to be able to perform full text searches. The table has 823 368 rows. Yesterday we had a server ...
4
votes
2answers
2k views

Why is dropping an index on MyISAM table so slow?

Why is dropping an index on MyISAM table so slow? I can't imagine I'd have social network with 700m records & dropping index. There are only one-column indexes, all of them are fulltext (I need ...
0
votes
1answer
251 views

Mysql : Cannot create Index:

Hi all we are having trouble to create/drop index(s) on a table I keep getting the following error: ERROR 1280 (42000): Incorrect index name '<>' Now the table has the following: 1. PRIMARY ...
2
votes
4answers
3k views

How to reduce mysql disk io?

mysql> explain SELECT * -> FROM (`items`) -> WHERE `states` = 'A' -> ORDER BY `updated_date` desc -> LIMIT 20520, 40; ...
0
votes
2answers
204 views

Sphinx indexer slows down the database: How to give it low priority?

All my tables use InnoDB, and I have set sphinx sql_range_step to the minimum, which is 128. This improved the performance a lot, but it is still very slow if you make a request right after a new step ...
0
votes
2answers
705 views

Can I backup a mysql index? On an innodb table?

I have several tables that are 10's of millions of rows, and have multiple indexes. When dumping and reloading the data it takes quite a while to reinsert because of the indexes, or to insert without ...
0
votes
1answer
125 views

Which is faster, key_cache or OS cache?

In a tb with 1 mil. rows if I do (after I restart the computer - so nothing it's cached): 1. SELECT price,city,state FROM tb1 WHERE zipId=13458; the result is 23rows in 0.270s after I run 'LOAD ...
2
votes
1answer
776 views

MySQL query is taking long time after index is rebuilt, but then works fast… why?

I have a simple table with 3M records and a simple unique index on an integer field. When selecing SELECT * FROM ThisTable ORDER BY ThatField LIMIT 10 It works very fast, as expected. When I insert ...
1
vote
3answers
136 views

MySQL FullText Weird Characters

It appears MySQL FullText index does not index the word 'C#'. Probably because the character '#' is removed and you are left with C, which is too short to index. My question is, how would I go about ...
3
votes
1answer
1k views

Mysql auto_increment reset WITHOUT rebuilding index?

There just HAS to be a way to reset the auto_increment value on a table without rebuilding the entire index. I have a table with over 2 billion rows in it that accidentally got an ID inserted into it ...
1
vote
1answer
1k views

How to create a table in MySQL with automatic numeration of rows?

I just want to have a table in which a first column is a monotonically increasing unique integer number. For example, if I have a table with 5 rows the indexes of the rows should be 1,2,3,4,5. And if ...