query optimization request

[Mashiah Davidson]

Colleagues,

any idea on how to speedup execution of the following query on toolserver?

#
# Non-existent orphaned categories (as identified by
categoryname prefix) linked from main namespace.
#
# INSERT INTO ref_orcat (cat)
# SELECT cl_to as cat
# FROM <dbname>.categorylinks,
# <dbname>.page
# WHERE LEFT( cl_to, isocl ) = isocsl and
# cl_to NOT IN (
# SELECT cat
# FROM orcat
# ) and
# cl_from=page_id and
# page_namespace=0
# GROUP BY cl_to;

here

SELECT 1+CHAR_LENGTH( @isolated_category_name ) INTO isocl;
SELECT CONCAT( @isolated_category_name, '/' ) INTO isocsl;

and @isolated_category_name is a language specific constant.

Tables from database <dbname> could be relatively huge.

mashiah

[Mashiah Davidson]

>            # Non-existent orphaned categories (as identified by
> categoryname prefix) linked from main namespace.

Well, I think the solution could be:

# INSERT INTO ref_orcat (cat)
# SELECT cl_to as cat
# FROM <dbname>.categorylinks,
# <dbname>.page

# WHERE cl_to > isocsl and
# cl_to < isocsl_ and

# cl_to NOT IN (
# SELECT cat
# FROM orcat
# ) and
# cl_from=page_id and
# page_namespace=0
# GROUP BY cl_to;

where

SELECT CONCAT( @isolated_category_name, '/' ) INTO isocsl;

#
# this string is just greater than any name of isolated category
#
SELECT CONCAT( @isolated_category_name, char(hex('/'+1)) )
INTO isocsl_;

I'm going to test this one right after completion of a long running
task I ran after a number of other fixes.

mashiah