Skip to main content

MySQL: Deleting with Left Join

dinke 6 Comments

Today I had to deal with one huge table and cleanup all data where foreign key doesn’t have it’s primary key match in original table, just to remind myself how sub-queries in MySQL are terrible slower than joins.

I have some script which generates domains from typos, so I have one table with original domains (master_domains) and other one (result_domains) with generated typo domains. Basically something like this:

mysql> describe master_domains;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| domain | varchar(255)     | NO   | UNI | NULL    |                | 
+--------+------------------+------+-----+---------+----------------+
2 rows in set (0.07 sec)

mysql> describe result_domains;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| domain    | varchar(255)     | NO   | UNI | NULL    |                | 
| master_id | int(10) unsigned | YES  | MUL | NULL    |                | 
+-----------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

Table result_domains has master_id which is foreign key reference to primary key (id) in master_domains table. Since I also have other scripts generating domains without typos (which store result_domains.master_id field as NULL), today I simple wanted to get rid of those masters without proper master_id reference in result table or in other words those master domains where result_domains.master_id is NOT NULL.

With sub-queries you could write query easily with something like this:

delete from master_domains where id not in 
(select master_id from result_domains_frontend)

It is good habit to always run select query before deleting big number of rows (just to make sure your query is written correctly) so I tried select query first:

select * from master_domains where id not in 
(select master_id from result_domains_frontend) limit 10

However, it took several minutes to run without any output so eventually I’ve decided to stop it. I know that sub-queries are much slower than joins, so decided to do try removal operation with left join.

Left joins are actually perfect weapon to find rows that exist in one (left) and doesn’t exist in other (right) table. They also have one big advantage over sub-queries – they are performing much faster, plus they are backward compatible with old prehistoric MySQL 5.x versions. However delete syntax is little bit tricky so after few trial and errors eventually I came out with this query:

delete master_domains.* from master_domains 
left join result_domains_frontend 
on master_domains.id=result_domains_frontend.master_id 
where result_domains_frontend.master_id is null ;

And voila after a while it came up with result:

mysql> delete master_domains.* from master_domains 
left join result_domains_frontend 
on master_domains.id=result_domains_frontend.master_id 
where result_domains_frontend.master_id is null ;
Query OK, 270558 rows affected (46.58 sec)
mysql>

MySQL: Moving table from one db to another

dinke Leave a comment

To move one table from one db to another, you can create new table (create table foo_new like foo) in db where you want to move table and then copy data with insert into/select query. However there is much easier way which is especially handy when you deal with big tables.

As you probably aready know, there is easy way to rename MySQL table just by issuing rename clause in alter statement:

ALTER TABLE foo RENAME TO new_foo;

You can also use RENAME TABLE syntax like this:

RENAME TABLE foo TO new_foo;

Now, when you need to move table from one db to another, all you have to do is to specify it’s current db and new db name as table prefix. For example if you want to move table foo from current db to new db you can issue queries like these:

ALTER TABLE currentdb.foo RENAME TO newdb.foo;

or

RENAME TABLE currentdb.foo TO newdb.foo;

Btw there is important difference between ALTER and RENAME statements in a way that with RENAME you can rename more than one tables at once. This comes handy if you want for example to swap names of two tables:

RENAME TABLE table1 TO temp, table2 TO table1, temp TO table2;

HTC Hero USB Tether on Mac OS X

dinke 112 Comments

Recently I got brand new HTC Hero – great android based phone. Since my previous phone (Nokia N73) has been working with Mac without any problem I was very disappointed to figure out that I have to deal with Windows for syncing my phone with computer and firmware upgrades.
After I upgraded it to latest official 2.73.405.4 ROM, I used Google Contacts/Calendar apps to sync my previous Nokia 73 stuff with Google and Hero, so having laptop<-->mobile connection to Internet (tethering) really sounded like something possible to achieve. A friend of mine with HTC Magic told me about way to connect to Internet with Wifi Tether application, which works on root-ed devices only. So after I passed trough complete rooting procedure explained here and here, I gained root access but still wasn’t able to make Wifi Tether work with Mac. Hero was visible as another AP, and even thought Mac was actually able to connect to it, I never got “outside” access to the Internet and eventually gave up. I found some threads that modaco custom ROM might be solution but figured that for newbie in android world like me, chances to brick brand new device while doing unsupported ROM upgrade are not so low so eventually I gave up.

Unlike for wifi tethering, USB tether for Hero and Windows comes “Out of the Box” (all you have to do is to turn on mobile sharing) but unfortunately it doesn’t work with a Mac. When you connect Hero to Mac, instead of new network device Mac see it as modem, but if you try to dial (like with *99#) you obviously get an error.
After I spent several hours reading endless discussions on various forums, today I finally managed to connect my MacBook Pro on the Internet with my HTC Hero mobile Internet (EDGE/GPRS/3G/HSDPA) connection.

Before I delve into instructions, please bear in mind that although this tutorial is based on HTC Hero and Mac running on latest (10.6.1) Snow Leopard, as far as I can tell it should be working with any other Android device or older version of Mac. I assume that you have MacPorts installed and that you are able to connect to Internet with your Mobile Phone.

Prepare your phone
————————–
You are going to install azilink on your phone. Since all tether applications are removed from Android Market, you have to download it directly from site. But before you do that, you have to make sure that installing applications from unknown sources is allowed. So Press Home, then Menu->Settings->Applications and make sure that “Unknown Sources” is checked. Also in Development menu make sure that “USB Debugging” is checked.

Now on your phone open browser and go to http://azilink.googlecode.com/files/azilink-2.0.2.apk , download file and follow install instructions. Once azilink application is installed start it and make sure that “Service Active” option is checked.

Prepare your Mac
————————–
Step1: TuneTap App
Download and install tuntap for Mac OS X (http://tuntaposx.sourceforge.net/) on the Mac and restart your mac.

Step2: Android SDK
Download Android SDK (http://developer.android.com/sdk/index.html) for Mac and unpack it somewhere on your machine. I used my $HOME dir (/Users/dinke) so I unpacked whole folder and named it android-sdk so full path is /Users/dinke/android-sdk. Since you are going to call tools from that folder, we have to put it into our $PATH variable by editing $HOME/.bash_profile file. I assume that you don’t have UNIX experience so this is step by step guide :

1) Open Terminal Applications
2) Type:

cd

and hit enter
3) You are now in your home dir (ie. /Users/dinke). Now you are going to edit .bash_profile file with pico editor :
Type:

pico .bash_profile

4) Add this line to last line of file:
export PATH=/Users/dinke/android-sdk/tools:$PATH
(replace /Users/dinke/android-sdk/tools with actuall path on your system)
5) Hit ctrl-x then answer Y and hit enter

Now you should be ready to call android tools (like we are going to do later) without specifying full system path.

Step3: OpenVPN2 App

Download and install openVPN2 . We will use MacPort’s port command for doing that. So again open terminal application and type:

sudo port install openvpn2

Now application will be automatically downloaded and compiled. This will take a while, and if this end without any error message you are good to go further 🙂

Step 4: azilink.ovpn file

1) Download http://azilink.googlecode.com/files/azilink.ovpn file
2) Create folder openvpn in $HOME/library folder (so you have new folder in for example /Users/dinke/Library/openvpn
3) Edit file azilink.ovpn and comment out line 8 with TCP_NODELAY (so it may looks like this):

dev tun

remote 127.0.0.1 41927 tcp-client
proto tcp-client
ifconfig 192.168.56.2 192.168.56.1
route 0.0.0.0 128.0.0.0
route 128.0.0.0 128.0.0.0
#socket-flags TCP_NODELAY
#keepalive 10 30
ping 10
dhcp-option DNS 192.168.56.1

4) Save file in new created in new created openvpn folder

Step 5: Modem script

1) Download script from http://pastie.org/405289 (there is download link on top right) and save it somewhere on your system. I assume you saved it under our home dir as modem.sh (/Users/dinke/modem.sh)
2) Give that script executable privileges. So open terminal application and type this:
chmod 777 /Users/dinke/modem.sh
(change path and script name according to your system)

Now we are almost done and we are ready to test actual connection. So connect your phone with Mac with USB cable, open terminal app and type this:

adb devices

You should get something like this:

dragan-dinics-macbook-pro:tools dinke$ adb devices
* daemon not running. starting it now *
* daemon started successfully *
List of devices attached
HT9FSL901734    device
dragan-dinics-macbook-pro:tools dinke$

If you get error (like command not found) check path setting for android sdk tools. If your device is not listed check that it is connected properly. If everything looks ok (you get device id listed) then we can connect our phone to Internet and run actual connection script. Make sure that your phone is connected to Mobile Internet (go to Menu->Settings->Wireless Controll and check Mobile Network checkbox), and then assuming that you’ve saved modem script under /Users/dinke/modem.sh you can run it by typing absolute path in terminal:

/Users/dinke/modem.sh

and you should get output like this:

dragan-dinics-macbook-pro:~ dinke$ /Users/dinke/modem.sh
Tue Nov 10 03:33:53 2009 OpenVPN 2.0.9 i686-apple-darwin10.0.0 [SSL] [LZO] built on Nov  9 2009
Tue Nov 10 03:33:53 2009 IMPORTANT: OpenVPN's default port number is now 1194, based on an official port number assignment by IANA.  OpenVPN 2.0-beta16 and earlier used 5000 as the default port.
Tue Nov 10 03:33:53 2009 ******* WARNING *******: all encryption and authentication features disabled -- all data will be tunnelled as cleartext
Tue Nov 10 03:33:53 2009 gw 0.0.0.0
Tue Nov 10 03:33:53 2009 TUN/TAP device /dev/tun0 opened
Tue Nov 10 03:33:53 2009 /sbin/ifconfig tun0 delete
ifconfig: ioctl (SIOCDIFADDR): Can't assign requested address
Tue Nov 10 03:33:53 2009 NOTE: Tried to delete pre-existing tun/tap instance -- No Problem if failure
Tue Nov 10 03:33:53 2009 /sbin/ifconfig tun0 192.168.56.2 192.168.56.1 mtu 1500 netmask 255.255.255.255 up
Tue Nov 10 03:33:53 2009 ./modem.sh up tun0 1500 1502 192.168.56.2 192.168.56.1 init
add net 0.0.0.0: gateway 192.168.56.1
add net 128.0.0.0: gateway 192.168.56.1
Tue Nov 10 03:33:53 2009 Attempting to establish TCP connection with 127.0.0.1:41927
Tue Nov 10 03:33:53 2009 TCP connection established with 127.0.0.1:41927
Tue Nov 10 03:33:53 2009 TCPv4_CLIENT link local: [undef]
Tue Nov 10 03:33:53 2009 TCPv4_CLIENT link remote: 127.0.0.1:41927
Tue Nov 10 03:34:03 2009 Peer Connection Initiated with 127.0.0.1:41927
Tue Nov 10 03:34:03 2009 Initialization Sequence Completed

If you get some errors make sure that you’ve passed correctly trough all needed steps. If no errors found, it is good time to test your new internet connection. You can do so by trying to ping some site or simple opening browser and visiting some Web Site. It should be working just fine!

My HSDPA connection with USB Tethering
Speedtest of my HSDPA connection with Telenor Serbia

Usefull Links:

http://androidsmartphone.de/apps/g1-als-usb-modem-ohne-root-auf-dem-mac/
http://thinkden.com/index.php/general/admin/75