Tag: mysql

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

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