Unanswered Question

Can you perform 'group by' on sets?

Let 'k7' be one set, and 'iis' / 'ids' be the second set:

select dept, count(dept) from users where dept="k7" or dept="iis" or dept="ids" group by 'dept'

With (approx.) output as:

 +---------+-------------+
 | dept    | count(dept) |
 +---------+-------------+
 | k7      | 23          |
 | iis/ids | 98          |
 +---------+-------------+

Do cross database joins cost more than intra-database joins? see: "Joining Across Multiple Databases" below.

Answered Questions

Joining Across Multiple Databases

Problem: I have two databases, a master database with all objects in a network, and a second database recording events . Both databases exist in the same SQL server, is it possible to do a join on the object database, and the event database?

Solution: Provided that both databases exist accessible to a common user, the answer is "yes".

If you log in to mysql as that common user, without specifying a database (or, alternatively, don't do a "select_db" type command in Perl, PHP, etc.) then you have access to both databases if you use fully qualified names.

 SELECT * FROM db01.tab01 AS d1, db02.tab02 AS d2
 WHERE  d1.machine_id = d2.id;

Finding Duplicates

Problem: I have a database full of email addresses and names for hundreds of people in a large mailing list. The mailing list has -exclusive- categories, and so any duplicates are bad.

Solution: (basic idea)

 SELECT t1.id, t1.name, t1.email
 FROM contact AS t1, contact AS t2
 WHERE t1.email = t2.email
 AND t1.id <> t2.id
 ORDER BY t1.email, t1.id ASC;

This is a de facto JOIN and thus it is optimized to the best of the engine's ability. Because of the number of comparisons (N^2 on a 1000 item list is a million operations) this is a heavy task. Creating an index on the "email" field in this case will help speed things up.

Before creating INDEX on email:

 real       user       sys
 --------   --------   --------
 0m5.468s   0m0.060s   0m0.015s
 0m5.411s   0m0.053s   0m0.020s
 0m5.572s   0m0.051s   0m0.023s

 ALTER TABLE contact ADD INDEX ( email );

After creating INDEX on email:

 real       user       sys
 --------   --------   --------
 0m0.375s   0m0.032s   0m0.041s
 0m0.361s   0m0.029s   0m0.044s
 0m0.372s   0m0.062s   0m0.012s

Okay, so it makes a big difference...

--fishy

insert entries into a table, updating existing entries

"REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a UNIQUE index or PRIMARY KEY, the old record is deleted before the new record is inserted."

-- from the mySQL Reference (offsite).

Code example:

 REPLACE INTO table ( id, otherstuff )
 VALUES ( "id|NULL", "other stuff" );