0

I have really big table (over 10 million rows) and query which returns a lot of data. I need to make it to run faster. So I tried to add covering indexes (which consisted with values from where clause and id) but there still was full table scan even after index hint to USE INDEX. Then I cut values in select (left only id) and added covering index, but still there was full table scan. How to avoid that full table scan? I tried to make covering index for all columns and got full index scan, but that solution was more longer then full table scan. Is there some other ways to optimize? I tried indexes, tried to remove not exists (changed for id not in) and it all makes worse time. I have indexes for Table1.id, table1.UserId, Table2.Id.

select t.id, t.Date, t.Added , t.NewId, t.UserId, t.Lost 
from Table1 t
where t.Added=0 and t.Lost=0 
   and not exists (select 1
                    from table2 n 
                    where n.Id=t.id and n.userId=t.userId and n.Added=0 and n.Del=0);
Olha Tkachuk
  • 17
  • 3
  • Did you try this composite index on `table2`? `INDEX(Del, Added, userId, Id)` Is `id the `PRIMARY KEY` in each table? – Rick James Jan 14 '22 at 02:56

1 Answers1

0

It's almost impossible to tell you anything since you have not shown how your table was defined including what the actual indexes you are using.

But one thing you might try is to replace you dependent subquery with a LEFT OUTER JOIN, which the MySQL engine might be able to better optimize:

select t.id, t.Date, t.Added , t.NewId, t.UserId, t.Lost 
from Table1 t
left join table2 n on n.Id=t.id and n.userId=t.userId and n.Added=0 and n.Del=0
where t.Added=0 and t.Lost=0 and n.Id is null;
  1. Create a multicolumn index on the following columns: Table1.id, Table1.userId, Table1.Added, Table1.Lost, Table1.NewId
  2. Create indexes on the following columns if they are not already indexed: Table2.Id, Table2.userId, table2.Added, Table2.Del
Booboo
  • 38,656
  • 3
  • 37
  • 60
  • I have already indexes for Table1.id, Table1.userId and Table2.Id. And with them and using left join it became possible to cut time in 2 (thanks a lot for this). So I don`t need covering indexes? But indexing other columns will be beneficial? – Olha Tkachuk Jan 13 '22 at 13:01
  • If you created a multicolumn index on *all the columns that your are selecting from Table1*, that would be a *covering index* and also include the columns I mentioned in item 1. of my list of columns and I would think only the index would need to be scanned instead of the actual rows. Then just be sure to have indexes on the columns I mentioned in the second list. – Booboo Jan 13 '22 at 13:14
  • I've updated the answer to make this more explicit. – Booboo Jan 13 '22 at 13:21