Count duplicate occurrences in MYSQL table based on value in two columns using having and inner join. I have a table that lists vehicles by owner. In the table I have owner_id and vehicle_type columns, I want to pull all owners that have both more than 1 car and more than 1 van. The query I put together appears to be working but it has some performance issues. 

My table is hosted in a MYSQL database.

Here's what I'm currently using, any recommendations on what I can change?

select * from (
  select owner_id, vehicle_type, count(*)
  from vehicles
  where vehicle_type = 'Car'
  group by owner_id, vehicle_type
  having count(*) &gt; 1
) t1
inner join (
  select owner_id, vehicle_type, count(*)
  from vehicles
  where vehicle_type = 'Van'
  group by owner_id, vehicle_type
  having count(*) &gt; 1
) t2
on t1.owner_id= t2.owner_id
You can reduce the queries to one:

select owner_id, sum(vehicle_type='Car') as cnt_car, sum(vehicle_type='Van') as cnt_van
from vehicles
where vehicle_type in ('Car', 'Van')
group by owner_id
having cnt_car &gt; 1 and cnt_van &gt; 1


Apart from that, the index that would provide the best performance would be a combined index on the columns (owner_id, vehicle_type).