Tag Archives: tips

MySQL delete duplicate lines (MyISAM and InnoDB)

Sometimes this task can be a real pain in the ass.. Heres a quick & dirty way to remove.

Let’s say you have the following table:

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| col_a | varchar(10)      | YES  |     | NULL    |                |
| col_b | varchar(10)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

With the following data:

+----+-------+-------+
| id | col_a | col_b |
+----+-------+-------+
|  1 | YY    | ZZ    |
|  2 | YY    | XX    |
|  3 | YY    | ZZ    |
|  4 | YY    | XX    |
|  5 | XX    | XX    |
+----+-------+-------+

Note: On this example I’ll assume a unique row is identified by the pair “col_a” + “col_b”.

If you want to delete duplicate rows and you don’t care for the PRIMARY KEY or which of the rows gets selected, you can add a UNIQUE INDEX.

For MyISAM engine tables

1
ALTER IGNORE TABLE `example_table` ADD UNIQUE INDEX `idx_uq` (`col_a`, `col_b`);

Doing this will automatically purge the duplicate rows instead of shouting an error because we’re using IGNORE.

Et voila:

+----+-------+-------+
| id | col_a | col_b |
+----+-------+-------+
|  1 | YY    | ZZ    |
|  2 | YY    | XX    |
|  5 | XX    | XX    |
+----+-------+-------+

Important Note on InnoDB engine tables

If your under MySQL 5.5/InnoDB this will not work due to a known “InnoDB index limitation“.

Luckily, someone at stackoverflow.com came up with a simple solution:

1
2
SET SESSION old_alter_table=1;
ALTER IGNORE TABLE `example_table` ADD UNIQUE INDEX `idx_uq` (`col_a`, `col_b`);
optimization-icon

Doctrine 1.2 (Symfony 1.4) performance tips & tricks

Sure Doctrine is awesome, but sometimes (every time?), it is also a memory killer. Hopefully there are some handy tricks that can be used on several occasions. Here are some Doctrine 1.2 performance tips & tricks I’ve collected over the time working with Symfony 1.4. Hope they’re useful to you guys!

If you know more performance tips, please leave them in the comments.

 

 

Tip #1

Create a task environment to run tasks and disable the profiler on databases.yml:

1
2
3
4
task:
  doctrine
:
    param
:
      profiler
: false

 

Tip #2

Enable Doctrine’s auto free query objects

1
Doctrine_Manager::connection()->setAttribute(Doctrine_Core::ATTR_AUTO_FREE_QUERY_OBJECTS, true);

 

Tip #3

Free Doctrine objects when they are no longer needed

1
2
$doctrine_object->free(true);
$doctrine_object = null;

 

Tip #4

Force Doctrine connection clear after performing needed Doctrine operations

1
2
3
4
5
6
7
Doctrine_Manager::connection()->connect();

// code code ...
$doctrine_object = Doctrine_Query::create()->from('Modal a')->limit(1)->fetchOne();
// code code...

Doctrine_Manager::connection()->clear();

 

Tip #5

Hydrate array when Model objects are not needed.

1
$doctrine_object = Doctrine_Query::create()->from('Modal a')->limit(1)->execute(array(), Doctrine_Core::HYDRATE_ARRAY);

 

Tip #6

Enable APC caching on Doctrine queries.

1
2
Doctrine_Manager::connection()->setAttribute(Doctrine::ATTR_QUERY_CACHE, new Doctrine_Cache_Apc(array('prefix' => 'fancyapcprefix_')));
Doctrine_Manager::connection()->setAttribute(Doctrine_Core::ATTR_RESULT_CACHE, new Doctrine_Cache_Apc(array('prefix' => 'fancyapcprefix_')));

 

Tip #7

Perform bulk inserts instead of one-by-one inserts.

1
2
3
4
5
6
7
8
9
10
11
$record1 = new Model()
$record1->setName('Example #1');
$record2 = new Model()
$record2->setName('Example #2');

$col = new Doctrine_Collection('Model');
$col->add($record1);
$col->add($record2);
$col->add($record3);
$col->add($recordN);
$col->save();

from this post

 

You can read more on Doctrine 1.2 performance here, here, here and here.

Submit a form with an input named “submit”

First of all…

The most common mistake made when defining the form HTML that a script will interact with follows from the existence of the shortcut accessors for form controls. It is to give the control a NAME (or possibly ID) that corresponds with an existing property of FORM elements. And the most common example of that is an INPUT element of type=”submit” with the NAME “submit”. Because the named controls are made available as named properties of the FORM element this INPUT element is made available under the property name “submit”. Unfortunately FORM elements already have a property with the name “submit”, it is the submit method that can be used to submit the form with a script. The misguided choice of name for the INPUT element effectively renders the form’s submit method unscriptable. And the same is true for all controls with names that correspond any with existing FORM element properties.

http://www.jibbering.com/faq/notes/form-access/#faComMis

 

So, unless you know what you’re doing do not name a form field “submit” – It will most likely break you’re javascript on that form.

Solution – Same domain

If you’re trying to submit to the same domain, just use Jquery’s $.ajax() and you should be Ok.

Solution – Cross domain

You can use YQL to do it, check the post here.

But… this solution kinda sucks, so if you know another way please feel free to comment!