Optmizing query

I have table with around 70 000 rows. There is 6000 rows that i need to find, change, and save (i must do this often, few tiems a week). Curently, i am doing it like this, and it las more than 2 minutes to finish: (any advice? :( )

  $query = mysql_query("SELECT meta_value FROM my_table WHERE meta_key = 'key' AND meta_value LIKE '%something%'")or die(mysql_error());

  $new = "soemthing something";

  while($row = mysql_fetch_array($query)){

    $old = unserialize($row['meta_value']);
    $new_meta_r = str_replace($old['color'],$new,$old);
    $new_meta = serialize($new_meta_r);

    $update_meta = mysql_query("UPDATE my_table SET meta_value = '$new_meta' WHERE meta_key = 'key' AND meta_value LIKE '%something%'")or die(mysql_error());
  }

Answers


Here are some ways that can help optimizing :

  • Try getting the ID besides the meta_value , and when using update just reffering to the ID.
  • Check if you really need to use the entire wildcard or you can make it post or prefixed.

Meaning :

#Full wildcard  

 SELECT * FROM TABLE WHERE COLUMN LIKE '%something%';  

#Postfix wildcard  

 SELECT * FROM TABLE WHERE COLUMN LIKE  'something%';  

 #Prefix wildcard  

SELECT * FROM TABLE WHERE COLUMN LIKE  '%something'; 
  • Make sure you're using the right data types, that varchars are the right size, using int instead of BigInt, etc.
  • Try moving the query to a Stored Procedure, Stored Procedures are compiled (SQL Code is not), making them faster than SQL code.

Try putting an index on 'meta_key' and try to work around having to use LIKE for these operations. Why do you need LIKE here? What's the exact requirement?


Besides storing the primary keys from the first query and using them in the update, you could, make a temporary table, do a batch insert to that table and then a join update on the primary table.

That way, you won't have to run a query in a cycle.

Creating Temporary Tables


I am not a php person, but on sql side I can suggest you something. There are some things that you can do,

Your SQL statement is,

SELECT meta_value FROM my_table WHERE meta_key = 'key' AND meta_value LIKE '%something%'

The things that you can do are,

  1. Add indexes on the key filed. There are two filed in your query meta_key, meta_value, try adding composite or separate indexes for both.

  2. The query Now check which clause in your where block returns more rows. put that before other one.

    Q1 : SELECT meta_value FROM my_table WHERE meta_value LIKE '%something%'

    Q2 : SELECT meta_value FROM my_table WHERE meta_key = 'key'

    if Q1 return's more rows than Q2 SQL statement, Then your SQL should be like below,

    SELECT meta_value FROM my_table WHERE meta_value LIKE '%something%' AND meta_key = 'key'


Need Your Help

What kind of Plugin is the New Playstation Share Blog using | Wordpress

wordpress plugins blogs

Love this feature http://share.blog.us.playstation.com/ Users are able to post requested features and then vote them up or down. Is this a plug-in that can be downloaded?

Logic to parse specific html tag in given string and build collection of different type of string

c# string logic

I want to write a logic for below scenario, need ideas on best implementation for it -