Slow query becuase of join on multiple columns

Related searches

I have 2 tables, deposit_slips & deposit_slip_books as in this example :

deposit_slips

id      number      deposited_at
1       10011001    2019-01-01
2       10011002    2019-01-02
3       1001200     2019-01-03
4       1001201     2019-01-04
4       1001203     2019-01-05

deposit_slip_books

id      number_start        number_end
1       10011001            10011020
2       1001200             1001210

I used this query :

select 
    deposit_slip_books.id, 
    deposit_slip_books.number_start,
    deposit_slip_books.number_end,
    COUNT(deposit_slips.number) AS used,
    MIN(deposit_slips.deposited_at) as first_usage,
    MAX(deposit_slips.number) AS last_entered
from deposit_slip_books
left join deposit_slips
    on deposit_slips.number >= deposit_slip_books.number_start
    and deposit_slips.number <= deposit_slip_books.number_end
    and CHAR_LENGTH(deposit_slips.number) = CHAR_LENGTH(deposit_slip_books.number_start) 
group by deposit_slip_books.id

The query gives me this result, which is what I need, but it is very slow, it takes about 1 minute to load and I have (63k deposit_slips) & (2k deposit_slip_books) :

id  number_start    number_end      used        first_usage     last_entered
1   10011001        10011020        2           2019-01-01      10011002
2   1001200         1001210         3           2019-01-03      1001203

I'll be grateful if you can show me a better way to do this, thank you.

EDIT :

Columns types :

  • number : VARCHAR
  • deposited_at : DATE

  • number_start : VARCHAR

  • number_end : VARCHAR

  • I need to have the CHAR_LENGTH, because I have deposit_slips with either 7 or 8 numbers, and they should be joined separately.

EDIT 2 :

Tables Schemas :

CREATE TABLE IF NOT EXISTS `deposit_slips` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `number` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `deposited_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `deposit_slips_number_index` (`number`),
) ENGINE=InnoDB AUTO_INCREMENT=617697 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `deposit_slip_books` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `number_start` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `number_end` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `number_start_number_end` (`number_start`,`number_end`),
) ENGINE=InnoDB AUTO_INCREMENT=175262 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I need to join these 2 tables to get a result of :

  • number_start : the column value from deposit_slip_books
  • number_end : the column value from deposit_slip_books
  • used : how many deposit_slips found in inside the range defined between ( number_start & number_end in deposit_slip_books table
  • first_usage : the first number found in deposit_slips table inside the range defined in deposit_slip_books table
  • last_entered : the last number from deposit_slips table that is inside the range found in deposit_slip_books table ( between number_start & number_end as I mentionned before )

be sure you have proper indexes on

table deposit_slip_books composit imdex on column (number_start, number_end)

and

table deposit_slips column (number )

and if is possible try avoid the char_length compare

select 
    deposit_slip_books.id, 
    deposit_slip_books.number_start,
    deposit_slip_books.number_end,
    COUNT(deposit_slips.number) AS used,
    MIN(deposit_slips.deposited_at) as first_usage,
    MAX(deposit_slips.number) AS last_entered
from deposit_slip_books
left join deposit_slips
    on deposit_slips.number >= deposit_slip_books.number_start
    and deposit_slips.number <= deposit_slip_books.number_end

group by deposit_slip_books.id

Optimize very slow SQL joins on multiple tables or use different , Your query using joins looks ok but it will help speed up the queries if you convert the timestamp_idx key to include session_id. This way the� All the mediumblob columns are JSON objects, but we haven't upgraded to MySQL 5.7.8 yet. All tables have the same columns and indexes. Each table contains in between several million and several billion rows. One of the problems I seem to have is that I can't limit the inner queries (if using them), and joins also don't appear to work.

Is it ok when you use deposited_at in where condition. To get results for a year, you have to run the foll query 12 times and you can index deposited_at to get performance

select 
    deposit_slip_books.id, 
    deposit_slip_books.number_start,
    deposit_slip_books.number_end,
    COUNT(deposit_slips.number) AS used,
    MIN(deposit_slips.deposited_at) as first_usage,
    MAX(deposit_slips.number) AS last_entered
from deposit_slip_books
left join deposit_slips
    on deposit_slips.number >= deposit_slip_books.number_start
    and deposit_slips.number <= deposit_slip_books.number_end
    and CHAR_LENGTH(deposit_slips.number) = CHAR_LENGTH(deposit_slip_books.number_start) 
where
deposited_at between '2019-01-01' and '2019-01-31'
group by deposit_slip_books.id

Learn SQL: Join multiple tables, This is crucial because before you join multiple tables, you need to identify Before we write the query, we'll identify the tables we need to use. When we join more than 2-3 tables in a query, and if we have a common column in all the tables, will there be any difference in the performace when we. specify the value to the common columns in all the tables. for ex: select e.* from emp e, dept d where e.deptno = 10 and d.deptno = 10; give value to one of the common column and join with the other

I ended up selecting everything from deposit_slip_books and looping through the collection in PHP (Laravel), I did something like this :

$data = DepositSlipBook::all()->map(function($book) {

    $deposit = DepositSlip::select([
        \DB::raw('COUNT(deposit_slips.number) AS used'),
        \DB::raw("MIN(deposit_slips.deposited_at) as first_usage"),
        \DB::raw('MAX(deposit_slips.number) AS last_entered'),
    ])
    ->where('deposit_slips.number', '>=', $book->number_start)
    ->where('deposit_slips.number', '<=', $book->number_end)
    ->where(\DB::raw('CHAR_LENGTH(deposit_slips.number)'), '=', strlen($book->number_end))
    ->first();

    $book->used = $deposit->used;
    $book->first_usage = $deposit->first_usage;
    $book->last_entered = $deposit->last_entered;
    return $book;
});

Not the best solution, but it takes 1 second to load, which is obviously better than 1 minute.

SQL Performance Tuning: 15 Go-To Tips to Fix Slow Queries, You're suffering from "Super Slow Query Syndrome,” and sometimes, your queries The WHERE clause connects two or more columns from different tables. related table joins are vital for filtering the elements of your SQL query. That will bog things down because the system will check every record in� The query takes around 5 - 6 secs to run. There are indexes for all the columns used in joins. The tables have 500k records. My question is: When I remove the columns tc.name, tc.id and tc.descr from the select, the query returns the results in less than a second. Why?

SQLite Self-join - Joining a Table to Itself, Because you cannot refer to the same table more than one in a query, you need The self-join compares values of the same or different columns in the same table. Note that the concatenation operator || concatenates multiple strings into a� I omitted the information because I didn't think it was important: As I mentioned, for brevity, I listed only the columns that are either part of the query, or part of an index. However, there are actually about 130 columns in the table (and view). Furthermore, the View also contains CLR UDFs on several columns that are not part of the query.

Performance, If the two tables were distributed on their join columns with statements similar to in subqueries will slow down query performance and cause Materialization� I have a query with multiple inner joins and left joins (about 40). When I run the query it takes about 20 seconds to return 3000 records. When I get the records without the joins it takes 1 second.

Finally I run a query that joins A & B on the foreign key columns and filters one of the column by a value. Oracle comes up with the plan below. The estimate on Ids 3, 4 & 5 is right on, but then, IMO, the cardinality estimate on the HASH JOIN is wrong, because the tables are in parent to child relationship the actual number of rows is equal to

Comments
  • Make deposit_slips.number a numeric column, or if text, then left pad all values to the same width using zero. Then, you can try to create an index to make the join faster.
  • why you are using CHAR_LENGTH(deposit_slips.number) = CHAR_LENGTH(deposit_slip_books.number_start) ???
  • @scaisEdge Because the number is probably stored as text, and a simple inequality by itself is not sufficient.
  • update your question and add your table schema ..
  • The number is stored as text, and I numbers with leading zeros, and I have the number column indexed in deposit_slips table
  • I need to have the CHAR_LENGTH, because I have deposit_slips with either 7 or 8 numbers, and they should be joined separately
  • but are two different values .. and then you don't need to check the length
  • Even if I removed the CHAR_LENGTH it is still extremely slow though.
  • have you added the composite index as suggested once you have remove the char_length check ???'
  • try update your question add both the tables schema and the explain plan for the query ..
  • Sorry, but this has nothing to do with what I asked.