I don't think it will be any better to count distinct values. I think
the query is just slow because the index lookups are slow. Is the
'word' column really 150 bytes? That's probably the culprit. How slow
is this, by the way? 370k rows in one table, verifying the
non-existence of index records in a 4M-row table with 150-byte index
values... what does "slow" mean for your application? How big is the
index for the 4M-row table (use SHOW TABLE STATUS)?
Russell Uman wrote:
There's no "using distinct", but there is "not exists", and in fact no
rows are
returned. Slow query log reports "#Query_time: 94 Lock_time: 0
Rows_sent: 0
Rows_examined: 370220"
EXPLAIN:
id select_type table type possible_keys key
key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 150 NULL
338451 Using index
1 SIMPLE t2 ref word word 150
t2.field 4
Using where; Using index; Not exists
These are two search tables (hence the large key_len i believe), one
with ~400K
rows, one row per search term the other with ~4M rows, relating search
terms to
content.
Perhaps I could optimize by doing a count(distinct) on each table and only
running the expensive query if the counts don't match?
Would I see any benefit by making these InnoDB tables?
Thanks for your help with this!
Baron Schwartz wrote:
Hi,
That is the right way, but if you show us the exact output of EXPLAIN
we can
help more. In particular, does it say "Using distinct/not exists" in
Extra?
Russell Uman wrote:
howdy.
i trying to find items in one table that don't exist in another.
i'm using a left join with a where clause to do it:
SELECT t1.field, t2.field FROM table1 t1 LEFT JOIN table2 t2 ON
t1.word = t2.word WHERE t2.word IS NULL;
both tables are quite large and the query is quite slow.
the field column is indexed in both tables, and explain shows the
indexes being used.
is there a better way to construct this kind of query?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
