What is best way to compare int and varchar fields in mysql?-CodePudding

I have a two large tables.

In the first table have indexed id field with type int. In the second table have indexed classId field with type varchar(50). I need to get all fields from first table which do not have classId in the second.

select 
    a.id, a.type, a.path, a.filename
from 
    assets a
where 
    a.type="folder" and 
    concat_ws("", a.path, a.filename) like "/product%" AND
    a.id not in (
        select 
           classId 
        from 
           gridconfigs g 
        where 
           g.type='asset' and
           g.name='Photo Attributes'
    );

I am trying to do it in that way but request is executing very slowly.

Any idea?

CodePudding user response：

Use Not exists... or if you need data from gridconfigs use a left join where g.classID is null

SELECT * 
FROM assets A 
WHERE a.type="folder" 
  AND  concat_ws("", a.path, a.filename) like "/product%" 
  AND NOT EXISTS 
      (SELECT 1 
       FROM gridconfigs G 
       WHERE A.ID = G.CLASSID 
         and G.type = 'asset' 
         and G.name = 'Photo Attributes')

As to why: Not exist and not in operate differently in the presence of NULL

Not exists can early exit once a single occurrence is found, in doesn't.

Read up:

Generally speaking: not in will not produce the desired results when Null is present; whereas not exists produces the desired results. They behave differently.

Also the concat_ws() means an index can't be used and will be slower. so you may be able to optomize this as well. But if it's good enough don't bother.