CODEWITHSUNDEEP
mysqljoin
blankip
blankip

Reputation: 340

Left joining two views is slow?

SELECT DISTINCT
viewA.TRID, 
viewA.hits,
viewA.department,
viewA.admin,
viewA.publisher,
viewA.employee,
viewA.logincount,
viewA.registrationdate,
viewA.firstlogin,
viewA.lastlogin,
viewA.`month`,
viewA.`year`,
viewA.businesscategory,
viewA.mail,
viewA.givenname,
viewA.sn,
viewA.departmentnumber,
viewA.sa_title,
viewA.title,
viewA.supemail,
viewA.regionname
FROM
viewA
LEFT JOIN viewB ON viewA.TRID = viewB.TRID
WHERE viewB.TRID IS NULL

I have two views with a about 10K and 5K records in them. They each come in very quickly - fraction of a second. When I try to get all of the records that are not in ViewB from ViewA, it works but it is very slow. All of the underlying TRID fields are same char set and all set to varchar (10) and indexed and tables are all Innodb. Right now the query is taking 16 seconds. Anything that I can do?

Upvotes: 2

Views: 4284

Answers (2)

Marcus Adams
Marcus Adams

Reputation: 53830

Normally, with JOIN, MySQL has to do a lookup for each joined record. Lookups are fast when using keys, but in your case, there aren't really any keys because the joined table is a view.

To try to get MySQL from running the query behind the second view once per record in the first view, we can use a subquery.

SELECT *
FROM viewA
WHERE TRID NOT IN (SELECT TRID FROM viewB);

This should allow MySQL to get all the TRID values for viewB in the subquery (in a temp table) then do a search over them for each record in viewA.

From MySQL docs:

MySQL executes uncorrelated subqueries only once. Use EXPLAIN to make sure that a given subquery really is uncorrelated.

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269445

It is hard to optimize queries with views in MySQL. My first suggestion is to get rid of distinct unless you absolutely know that it is needed.

Then you might compare the performance with this query:

select viewA.*
from viewA
where not exists (select 1 from viewB where viewB.TRID = viewA.TRID);

It is hard to say whether one will be better than the other, but it is worth trying to see if this is better.

Upvotes: 0

Related Questions