0

I am using MySQL version 5.6.10

I have a very very large table large_table and a relatively small table small_table

The small_table has only the records of the date 2019-11-20

There is a column I want to update in small_table whose entry is in large_table. I have a unique key using which I would find the row and then get the column from large_table and update it in `small_table.

I have run the following query

update 
    small_table, large_table 
set 
    small_table.start_time = large_table.start_time 
where 
    small_table.uuid = large_table.uuid 
;

The above query is taking very very long. I was wondering if I can improve the query by specifying the range of date in large_table like

update 
    small_table, large_table 
set 
    small_table.start_time = large_table.start_time 
where 
    small_table.uuid = large_table.uuid and
    large_table.time >= '2019-11-20' and large_table.time < '2019-11-21'
;

Would the above query improve time of execution ?

Basically what I want to know is would it make a difference to specify range and match both or the range does not matter when the match is specified.

Also, as @danblack mentioned in the comment, I would like to add one more question.

I could tell you if I have indexing on small_table or large_table but I do know what impact it would have in a different case.

So, I would also like to know how would result be affected if

  1. large_table had indexing, but small_table didn't
  2. small_table had indexing, but large_table didn't
  3. Both small_table and large_table had indexing
  4. Both small_table and large_table didn't have indexing
  • 1
    Which indexes are available will make a bit difference. SHOW CREATE TABLE small_table; and same for large_table. For estimating time, try EXPLAIN {query} however it won't work for an UPDATE query in 5.6, so rewrite the query as SELECT statement covering the same critiera and rows for the purpose of EXPLAIN. Please enter my.cnf configuration in the question - it might help explain why so slow. – danblack Nov 28 '19 at 2:59
  • @danblack updated the question – GypsyCosmonaut Nov 28 '19 at 3:08
  • @GypsyCosmonaut - "had indexing" does not tell us anything. What column or combination of columns were indexed? What was the PRIMARY KEY (which is also an index)? – Rick James Nov 28 '19 at 8:18
1

As you indicated by the small_table only containing one range, adding the date adds a hint that could be used in some cases. As worst it will just add a quick condition to an already retrieved row.

'had indexing' really doesn't help, it matters what the index begins with. Indexes can have multiple parts and the order matters significantly. An index with a 'prefix of A' could include A as the entire index.

The indexing on the small_table is largely a second consideration as the entire table needs to be scanned.

A uuid as a prefix on an index (or primary) key should be considered the minimium on the large_table. In this case the small table will be scanned and each uuid will be looked up in big table.

If a time is a prefix of a index (or primary key) of the large_table, date criteria in the index be searched and matched against the small_table. It depends here if the optimizer thinks the date range in the table is bigger or small than the small_table. If there is no uuid indexes this is the only optimal solution.

If there are no indexes, much slowness will happen, i.e. the large_table will be entirely scanned for each element in small_table

To check what will be used check:

EXPLAIN
SELECT small_table.uuid, small_table.time, large_table.uuid, large_table.time
FROM small_table, large_table
WHERE 
    small_table.uuid = large_table.uuid 
    AND large_table.time >= '2019-11-20' and large_table.time < '2019-11-21'
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.