logo
down
shadow

Very Slow Select / Join Query for table on Itself


Very Slow Select / Join Query for table on Itself

By : Morketh
Date : December 05 2020, 12:18 PM
hope this fix your issue There is no possible key because you don't have any WHERE conditions.
It is running slow because of the dependent subquery which, although fast in this case, still has to be run for each record in the result (the entire table). In MySQL dependent subqueries are very slow compared to joins.
code :


Share : facebook icon twitter icon
MySql join after select - Slow query

MySql join after select - Slow query


By : Dherya
Date : March 29 2020, 07:55 AM
it helps some times Use temp table for list of tracks and join that temp table with main query instead of using 'IN' clause. Generally, avoid IN clauses, as the number of items within the list goes up the query plan might change.
Creating index on 'track' column of performances table would give the desired behavior (query won't join the 'tracks' on all 100,000 rows.)
Why is this SQL (join) query slow when joining on a column that uses select max?

Why is this SQL (join) query slow when joining on a column that uses select max?


By : Tom Brehme
Date : March 29 2020, 07:55 AM
around this issue The query's slow because you're doing what's called a correlated subquery--it's running that max for each row.
Try something like this:
code :
select
    item.id as item_id,
    item.name as item_name,
    item.retail_value as item_retail_value,
    item.vendor as item_vendor,
    bid.bid_amount as bid_amount,
    bid.bidder_name as bid_bidder_name,
    bid.bidder_phone as bid_bidder_phone,
    bid.operator_name as bid_operator_name
from 
    item
    left outer join (
        select 
            item_id, 
            MAX(bid_amount) maxamount 
        from 
            bid 
        where 
            status = 'OK' 
        group by 
            item_id
    ) b1 on
        item.id = b1.item_id
    left outer join bid on
        bid.item_id = item.id
        and bid.bid_amount = b1.maxamount
Very slow mysql query: left join multiple table and where clause for each table

Very slow mysql query: left join multiple table and where clause for each table


By : William Xu
Date : March 29 2020, 07:55 AM
like below fixes the issue Try this: Convert your left joins to a bunch of WHERE EXISTS sub-queries and drop the GROUP BY, because (that's my suspicion here) that's what you really wanted to express.
code :
select 
    jj.id, jj.imgtitle, jj.alias
from 
    jjtable jj
    -- you could drop this join, you don't do anything with jjtable_catg
    inner join jjtable_catg jjc on jjc.cid = jj.catid
where
    jj.published = 1
    and jj.approved  = 1 
    and jjc.cid in(4, 10)
    and exists (
      select 1 
      from jjtable_map m inner join jjtable_tags t on m.tid = t.tid 
      where m.picid = jj.id m.delete_flag = 0 and t.tid in (77) and t.delete_flag = 0
    )
    and exists (
      select 1 
      from jjtable_map_per m inner join jjtable_tags t on m.tid = t.tid 
      where m.picid = jj.id m.delete_flag = 0 and t.tid in (28,36) and t.delete_flag = 0
    )
    and exists (
      select 1 
      from jjtable_map_fea m inner join jjtable_tags t on m.tid = t.tid 
      where m.picid = jj.id m.delete_flag = 0 and t.tid in (87,90) and t.delete_flag = 0
    )
    and exists (
      select 1 
      from jjtable_map_ag m inner join jjtable_tags t on m.tid = t.tid 
      where m.picid = jj.id m.delete_flag = 0 and t.tid in (98,99) and t.delete_flag = 0
    )
    and exists (
      select 1 
      from jjtable_map_fa m inner join jjtable_tags t on m.tid = t.tid 
      where m.picid = jj.id m.delete_flag = 0 and t.tid in (104,107) and t.delete_flag = 0
    )
order by 
    case when date(jj.date) > date_add(date(now()), interval -14 day) then jj.view end DESC, 
    jj.id DESC
MYSQL SELECT query very slow - use of JOIN possible?

MYSQL SELECT query very slow - use of JOIN possible?


By : user259108
Date : March 29 2020, 07:55 AM
this will help This working query is very slow (almost 30 sec execution) and I saw that the JOIN clause could improve speed. , Try this:
code :
SELECT club, COUNT(*) AS total,
    SUM(`category` IN ('U11', 'U13', 'U15', 'U18')) AS juniors,
    SUM(`category` IN ('O40', 'O50', 'O60', 'O70')) AS seniors
FROM players
GROUP BY club
left join same table twice slow query

left join same table twice slow query


By : นพ รอดกระจ่าง
Date : March 29 2020, 07:55 AM
I hope this helps . First, no need to use the order by clause in derived tables.
Second, your first derived table simply selects all the records in the table, so I don't see the point of it.
code :
LEFT JOIN xxs
               ON xxs.yy_type = zz.zz_coin
LEFT JOIN (SELECT xx_coinType AS yy_type, xx_sell AS old_sell_xx
           FROM xxs
           WHERE xx_time < UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 24 HOUR))
           ) AS old_xxs
               ON old_xxs.coin_type = zz.zz_coin
LEFT JOIN (SELECT xx_coinType AS yy_type, xx_sell AS sell_xx
           FROM xxs
           WHERE xx_time >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 24 
           ) AS xxs
               ON xxs.yy_type = zz.zz_coin
LEFT JOIN (SELECT xx_coinType AS yy_type, xx_sell AS old_sell_xx
           FROM xxs
           WHERE xx_time < UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 24 HOUR))
           ) AS old_xxs
               ON old_xxs.coin_type = zz.zz_coin
Related Posts Related Posts :
  • select the records with the highest amount
  • Changing Mysql golbal variable
  • How to create Effective Indexing on MySQL
  • `require': Incorrect MySQL client library version
  • How to save a FirstName of a Person efficiently without duplicate entries
  • Can I store my own sql query by mysql special function and execute stored query later?
  • MySQL comma separated field query
  • Why does the same exact query produce 2 different MySQL explain results?
  • Suppress warning output in bash
  • Doctrine "on update CURRENT_TIMESTAMP" annotation (Symfony2)
  • Designing "Friend" relationships across two mysql tables
  • Importing sql file using phpmyadmin in EasyPHP
  • Easy PHP and mysql ..php cannot see database on local pc (all works on server)
  • MySQL join optimization - bypassing filesort
  • How to Select record with the maximum value in a GROUP BY?
  • MySQL Innodb Full text Proximity Search Gives Horrible Performance
  • MySQL best approach for db normalising, relationships and foreign keys
  • How to update one table based on the count value of another table while matching ids?
  • MAMP Pro 5.1.1-terminates MySQL when quitting
  • SQL - Get date of most votes cast
  • "Fuzzier" search in mysql
  • Tracing mysqld Using SystemTap
  • SELECT + INSERT + Query Cache = MySQL lock up
  • SQL vs. NoSQL for medium complexity search systems
  • SQL Error when creating table
  • Is it possible to write a query to compare rows to other rows in same table?
  • Display all duplicate records based multiple columns
  • summing time column in mysql
  • InnoDB: Cannot open table
  • Why is IN() considered O(logN) operation?
  • Can a deadlock occur on commit?
  • Is there a fast way to do a '%phrase%' search in any DB?
  • Is using an IN over a huge data set a good idea?
  • How can I know if a table is already arranged in ascending order in MySQL?
  • MySQL - Designing database for a simple game
  • Why am I getting "Host '192.168.1.220' is not allowed to connect to this MySQL server"?
  • what type of password hash? possible mysql hash?
  • MySQL - Using a date range vs functions
  • Using IS NULL and COALESCE in OrderBy Doctrine Querybuilder
  • How to merge MySQL columns into one column if there are empty values in the table?
  • Cardinality Confusion
  • Renaming column multiple ids in SELECT
  • Group all from and to messages per user
  • How to handled the null values in WHERE IN condition in mysql
  • Is it okay to update primary key field to shift a record to the top?
  • Stop query through pdo
  • How to check whether particular value in field in MySQL exists
  • Execute a statement for every table in a database
  • insertion 74G data into mysql table cost more then 2 days, how to improve insert performance
  • Where can I see the differents "Hibernate Console Configurations" in Eclipse
  • MySql | relational database vs non relational database in terms of Performance
  • Exporting mysql database using mysqldump including procedures
  • Trigger MySQL not working
  • How do I update values for individual rows quickly?
  • MySQL join query with multiple where condition
  • MySQL stored procedure is not working
  • MySQL - Order By exact match at the end
  • Select max date or null date from same table MySQL
  • Best solution workaround for needing a mysql event on insert?
  • MySQL select several columns of several tables at the same time without using JOIN
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org