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?
--
russell uman
firebus
((((d-_-b))))
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
