MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Speed Up Queries / Question No: 49

Why my update query works slow in MySQL?

My update query works slow. The sample Table is as follows:
CREATE TABLE `project` 
( `id` int(11) NOT NULL, `score` double NOT NULL,
`project_rank` int(11) NOT NULL, PRIMARY KEY (`id`)
)
ENGINE=MyISAM;
The table has 2M rows when I run the following query:
SET @rank:=0;
UPDATE project SET project_rank=@rank:=rank+1 ORDER BY score DESC, id;
The process takes long time to update. Any idea to speed up this query.

Answer No: 49

You may apply following tricks:
Check what happens if you drop the index when you do the update? Does it run faster? If so then the index is slowing you down. In this case you should increase the key_buffer_size to 756M.
If there isn't much difference, try the update without the "ORDER BY" clause. If it runs considerably faster, then you need to increase your sort_buffer_size. For more help on optimizing the sort, the see the link: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html.

Related MySQL FAQs to the Above FAQ

What-is-better-query-to-update-a-table What is better query to update a table?

How-to-get-all-dates-between-two-dates How to get all dates between two dates?

How-to-speed-up--multi-table-based-delete-query How to speed up multi table based delete query?

What-is-query-cache-in-MySQL What is query cache in MySQL?

What-is-table-cache-in-MySQL What is table cache in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.