Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

cooperino's avatar

Is there a way to make this query faster?

This is the same query from my previous posts:

DB::table('items')->select('items_to_list.user_id', 'items.item_code')
->distinct('items_to_list.user_id')
->leftJoin('items_to_list','user_id', '=', 'items.id')
->where('items_to_list.status', '=', 0)
->where('items.deleted', '=', 0)
->get()

If items_to_list table has 100,000 rows, this query takes about 0.8 seconds. Then with the additional code, it totals for slightly over 1 second to fetch this data. Is there a way to optimize this query and make it run faster?

Thanks!

0 likes
10 replies
Sinnbeck's avatar

My best suggestion is looking at indexes. How are they now? Did you test it with explain?

1 like
sr57's avatar

Can you share the sql generated by this query?

1 like
Tray2's avatar

This is what I would try

  1. Remove the distinct since it makes the query slower.
  2. Read the indexes part of this blogpost
  3. Run an explain on the query to see which indexes are needed.
  4. Add those indexes one by one.
1 like
cooperino's avatar

thanks everyone, there was indeed a missing index, after adding the one with the help of EXPLAIN, it seems to be fast. I have to use DISTINCT in my case. So for now the index did optimize it and it is better.

Tray2's avatar

@cooperino I suggest trying to rewrite the query so that you don't need to use distinct, if possible,

1 like
cooperino's avatar

@Tray2 But if I have to get DISTINCT results, is there a way in SQL to simulate a DISTINCT without actually using it that is faster?

Tray2's avatar

@cooperino That depends on what data you have in your tables. Do you have multiples of the same user_id in the items_to_list table?

Snapey's avatar

there is a bit of a code smell on the join. Why is it joined by user_id being the same as item.id ?

1 like
cooperino's avatar

@Snapey Oh wow it's my mistake when copying the query you are right. It's actually item_id (the actual query in my code is correct)

Please or to participate in this conversation.