How to avoid full table scan in mysql query with large output

Question

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); 

Answer 1

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