Development

MySQL: Delete using join

For deleting orphaned children in a relational database I tried deleting those children using a ‘LEFT JOIN’ on the ‘mother’ table to find the children. With the select I used the normal approach and found all those poor kids. Changing the SELECT * into DELETE did not work. You’re not allowed to DELETE on the used table. First I thought a subquery could help me, NOT 🙂

The solution is to use the keyword USING.

An example:

DELETE FROM t1 USING t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL

In this way you can use as many joined tables for deleting, without affecting those joined tables

After testing I found out this also worked for me:

DELETE t1 FROM t1 LEFT JOIN t2 ON t2.id = t1.id WHERE t2.id IS NULL

I didn’t use the ‘FROM’ and don’t use aliases for your table you wish to clean. That won’t work!

Auteur:
Publicatie datum:
13 oktober 2009
Reacties:
1 Reactie

WordPress XML response issues (-32700:parse error)

Is wordpress giving you -32700:parse error. not well formed? Or is xmlrpc.php bothering you while getting getCategories function XML RPC call?
I did and i’ve found the problem:

In the IXR_Message class, the class which I use and also is recommended to parse the XML response of xmlrpc.php, uses preg_replace in the function parse() (IXR_Message->parse();). In PHP5 there has been some restrictions introduced to recursive regular expressions with Perl. Some parameters has been introducted to your php.ini (backtrack limit) and one of them is preventing me to interpret the XML-feed I get from my WordPress. Perhaps this one is very big, but i really need it, so:

Change at the beginning of your script your settings for example:
ini_set('pcre.backtrack_limit', 10000000);

You will see that the IXR_Message class is happy with the result it gets from the first lines of its parse function.

If you want to test if the backtrack limit is bothering you use the preg_last_error() function:

if (preg_last_error() == PREG_BACKTRACK_LIMIT_ERROR) {
print 'Backtrack limit was exhausted!';
}

The code in the parse() function is:
$this->message = preg_replace('/<\?xml(.*)?\?'.'>/', '', $this->message);

Auteur:
Publicatie datum:
14 juli 2009
Reacties:
Geen Reacties

flash debugger no connection

No connection to your local flash debugger when using flex in vista? Showing the message: “Waiting for Flash Player to connect to debugger”? Perhaps your reference to localhost in windows\system32\drivers\etc\hosts has been removed.
Simply add 127.0.0.1 localhost to your hosts file and your debugger can connect.

Costed me over more than 2 hours to find it out, hope you will find this solution faster!

Auteur:
Publicatie datum:
8 juli 2009
Reacties:
Geen Reacties

CouchDB: database the easy way

Voor een project ben ik bezig om verschillende databases te bekijken. Een daarvan is CouchDB (Apache CouchDB is a distributed, fault-tolerant and schema-free document-oriented database accessible via a RESTful HTTP/JSON API.)

Op 30 juni ga ik naar Kings of Code waar ook Chris Anderson zal spreken. Om niet geheel onvoorbereid te zijn heb ik een zoektocht gedaan naar informatie over CouchDB en hoe ik dit zou kunnen toepassen of hoe anderen het nu toepassen. Hieronder de lijst met links die me zijn opgevallen of hebben geinteresseerd. Dit in het geval je zelf een zoektocht gaat ondernemen richting CouchDB. Mocht je zelf nog tips hebben van goede sites, please let me know via de comments!

Auteur:
Publicatie datum:
22 juni 2009
Reacties:
1 Reactie

WordPress slow?

WordPress slow? Check your plugins MySQL database. I’ve checked the slow-log and discovered some slow queries.

I did some very basic adjustments, maybe not all very well overthought, but with dramatically speedup results.

wp_ratings checks a lot on both rating_postid en rating_ip, but by default no index so:
Slow Queries:
SELECT rating_username, rating_rating, rating_ip FROM wp_ratings WHERE rating_postid = 487;
SELECT rating_ip FROM wp_ratings WHERE rating_postid = 487 AND rating_ip = '83.80.25.124';
Solution:
ALTER TABLE `wp_ratings` ADD INDEX ( `rating_postid` )
ALTER TABLE `wp_ratings` ADD INDEX ( `rating_ip` )

wp_term_taxonomy from the very powerfull plugin YARPP (related posts plugin) as explained in http://wordpress.org/support/topic/192707 misses an index
Slow Query:
SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') AND tt.count > 0 ORDER BY t.name ASC;
 Solution:
ALTER TABLE `wp_term_taxonomy` ADD INDEX ( `taxonomy` )

wp_options doesn’t have an index on autoload (a core query), but the next query runs lots of times. Of course it is cached, but why not speed it up as much as possible.
Slow Query:
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
Solution:
ALTER TABLE `wp_options` ADD INDEX ( `autoload` )

Any comments are very welcome, this are just quick and dirty solutions, that perhaps need more time and study, but for now i’m happy!

Auteur:
Publicatie datum:
25 februari 2009
Reacties:
1 Reactie
Nieuwere artikelen
Oudere artikelen