CODEWITHSUNDEEP
phpmysql
valicu2000
valicu2000

Reputation: 431

Slow table update

I have a product table in OpenCart with a total 57,000 records. It looks like: 

 Field  Type    Null    Key     Default     Extra   
product_id  int(11) NO  PRI     NULL    auto_increment
model   char(64)    NO      NULL    
sku     char(100)   NO  UNI     NULL    
upc     char(1) NO      NULL    
ean     char(1) NO      NULL    
jan     char(1) NO      NULL    
isbn    char(1) NO      NULL    
mpn     char(1) NO      NULL    
location    char(1) NO      NULL    
quantity    int(4)  NO      0   
stock_status_id     int(11) NO      NULL    
image   char(255)   YES         NULL    
manufacturer_id     int(11) NO  MUL     NULL    
shipping    tinyint(1)  NO      1   
pret_brut   decimal(15,2)   NO      0.00    
price   decimal(15,2)   NO      0.00    
points  int(8)  NO      0   
tax_class_id    int(11) NO      NULL    
date_available  date    NO      NULL    
weight  decimal(15,8)   NO      0.00000000  
weight_class_id     int(11) NO      0   
length  decimal(15,8)   NO      0.00000000  
width   decimal(15,8)   NO      0.00000000  
height  decimal(15,8)   NO      0.00000000  
length_class_id     int(11) NO      0   
subtract    tinyint(1)  NO      1   
minimum     int(11) NO      1   
sort_order  int(11) NO      0   
status  tinyint(1)  NO  MUL     0   
date_added  datetime    NO      0000-00-00 00:00:00 
date_modified   datetime    NO      0000-00-00 00:00:00 
viewed  int(5)  NO      0

Indexes that are used:

PRIMARY BTREE   Yes No  product_id  56399   A   No <br/>
sku BTREE   Yes No  sku 56399   A   No<br/>
manufacturer_id BTREE   No  No  manufacturer_id 386 A   No<br/>
 +status    BTREE   No  No  status  1   A   No<br/>
 +date_available    440 A   No<br/>

... the last one (with the "+") is an index for multiple fields (I don't know the technical name for it).

The problem is that when I run a query like "UPDATE product SET stock_status_id=5, quantity=0, status=0" on that table, I get through PHP's microtime() function somewhere between 0.8 - 1.5 seconds. Perhaps I'm a bit paranoid, but it seems too much. Is there any way to improve the update time?

LE:

I want to disable all products in that table and then update only the ones that active. I don't know if this is the best way to do it, but I think is better that to check each one. Is there any other quicker way?

Upvotes: 0

Views: 65

Answers (2)

O. Jones
O. Jones

Reputation: 108839

Your UPDATE query, as you've formulated it, updates all the rows in your table. It does it at a rate of one row every two hundred microseconds or so. That is excellent performance.

You could probably get it to go a little faster by doing this:

UPDATE product SET stock_status_id=5, quantity=0, status=0
 WHERE status <> 0
    OR quantity <> 0
    OR stock_status_id <> 5

That will avoid updating rows already reset.

Upvotes: 1

A l w a y s S u n n y
A l w a y s S u n n y

Reputation: 38552

Why not chunk your records using WHERE,Using the WHERE clause will improve your update time

try like this 

UPDATE product SET stock_status_id=5, quantity=0, status=0 
    WHERE 
status!=0;

Upvotes: 2

Related Questions