Mysql - Left Join Takes Too Long, How to Optimize Query

mysql - query with multiple left joins takes too long

Create indexes, non indexed reads are slower than index reads.

To determine exactly what is causing you slow performance, the best tool
is to use the "query plan analyzer":

Have a look here:
mySql performance explain

Try creating indexes on the most obvious reads that will take place.Look at the fields that play a role when you join and also your where clause. If you have indexes on those fields your performance should increase, if non index reads were taking place.

If this is still a problem it is best to see how mySQL is fetching the data,
sometimes restructuring your data or even maybe altering the way you are queuing
can give you better results.

eg. Create index's for: aa.manufacturer_model, aa.manufacturer, aa.model and mm.hardware_type

left join taking too long

For this query:

SELECT table1.prod_id, table2.wh, table1.month, table1.year, volume*share AS new_volume
FROM table1 LEFT JOIN
     table2
     ON (table1.prod_id = table2.prod_id) AND
        (table1.month = table2.month) AND
        (table1.year = table2.year)

You want an index on table2:

create index idx_table2_prod_month_year on table2(prod_id, month, year)

You can also include wh2 and any other columns in the select that come from table2.

MySQL left Join taking too much time

It looks like I was missing unique in the other 2 tables, setting vin as unique for all tables and not only the first fixed the problem

Too long time execution for LEFT JOIN query

There are a few things to note about this:

1. There is no benefit in doing an outer join, when you have a non-null condition on the joined field(s) (in your case sp.id_store = 3). Since outer joins are more costly than inner joins, use the latter in this case: inner join. The result is the same, but probably faster.

2. If on the other hand you had hoped to list all products by doing the outer join, then your query is incorrect. You then must move the condition out of the where clause into the on clause, like this:

   LEFT JOIN stores_product AS sp
          ON p.reference = sp.reference 
         AND sp.id_store = 3
   

3. The join condition does not look as expected. Normally, you would expect sp.id_product = p.id. But in comments you explain these two fields are unrelated. That is a very confusing way of naming things. You should consider storing a foreign key which references the primary key in the product table.

4. Depending on how your data is distributed, you will get a benefit from one of the following two indexes -- which you need to create: stores_product(id_store, reference) or stores_product(reference, id_store).

5. Obviously product(id) should be a primary key.

Create the missing indexes, and look at the execution plan with explain select ... and see which are actually used.

LEFT JOIN query with COUNT takes so long to execute

The left join will multiply rows and then you condense them back using group by. Assuming that there is one artist and cover per album I would try counting the songs inside the select clause:

SELECT album.*, artist_aka, artist_address, cover_filename, (
    SELECT COUNT(*)
    FROM songs
    WHERE song.song_album_id = album.album_id
) AS TotalSongs
FROM album
LEFT JOIN artist ON album.album_artist_id = artist.artist_id
LEFT JOIN song_cover ON album.album_cover_id = song_cover.cover_id
ORDER BY album_plays DESC
LIMIT 0, 20

mysql left join takes too long

Try this one (without the GROUP BY):

SELECT 
    upd.*,
    usr.username AS `username`,
    usr.profile_picture AS `profile_picture`
FROM 
        updates AS upd
    LEFT JOIN 
        users AS usr 
            ON  upd.uid = usr.uid
WHERE 
    upd.deleted='0' 
  AND 
    ( upd.uid='118697835834'
   OR EXISTS
      ( SELECT *
        FROM   subscribers AS sub 
        WHERE  upd.uid = sub.suid
          AND  sub.uid = '118697835834'
      )
    )
ORDER BY upd.date DESC
LIMIT 0, 15

---

At least the columns that are used in Joins should be indexed: updates.uid, users.uid and subscribers.suid.

I would also add an index on subscribers.uid.

---

Related Topics