MySQL Forums
Forum List  »  Partitioning

Re: ERROR 1025 (HY000): Error on rename of '.\reporting\#sql-a2c_1' to '.\reporting\event' (errno: -1)
Posted by: Rick James
Date: September 01, 2014 08:28PM

First, to make it more readable:
SELECT  e.`time`, m.`model_name`, m.`network_address`, c.`creator_name`,
        e.` ype`, e.`event_key`, ss.`user_name`, e.`event_msg`
    FROM  `reporting`.`event` AS e
    JOIN  `reporting`.`model` AS m ON e.`model_key` = m.`mode _key`
    JOIN  `reporting`.`creator` AS c ON e.`creator_id` = c.`creator_ID`
    JOIN  `reporting`.`v_security_string_accessibility_by_landscape` AS ss
         ON    (m.`landscape_h` = ss.`landscape_h`)
          AND  (m.`security_string` = ss.`security_string`)
    WHERE  e.`time` >= '2013-12-10 00: 1:00'
      AND  e.`time`  < '2013-12-10 23:59:01'
      AND  ss.`user_name` = 'ad_knatan'
    ORDER BY  e.`time` DESC

That is a strange time range??

Event (e) is the largest table, correct? The optimizer may start with other table(s) first. Do EXPLAIN SELECT ... to see what order it hits the tables.

Does that time range lead to only a small number of rows in Event? If so, we may need to force the optimizer to use Event first:
SELECT STRAIGHT_JOIN ...
FROM event AS e
JOIN model AS m ON ...
JOIN v_security_string_accessibility_by_landscape AS ss ON ...
JOIN creator AS c ON ...
WHERE ...

event or v_security_string_accessibility_by_landscape should come first in the EXPLAIN. This is because there is a WHERE clause that would filter out rows there. Unfortunately the other table with WHERE-clause filtering is two hops away (model is in the way), so `model` needs to come second.

Don't do the STRAIGHT_JOIN unless there the EXPLAIN refuses to put the tables in a good order.

Since the ORDER BY is on e.time, and e.time is being filtered, `event` is probably the best first table, in spite of its size.

Now comes the problem of indexes.

Assuming `event` comes first, then partitioning on `time` has _no_ advantage over the existing INDEX(time).

With `model` second, it needs INDEX(model_key). (Based on the name, I would guess it is the PRIMARY KEY, which is even better.)

Then ss needs an index starting with landscape_h. Actually this may be even better:
INDEX(user_name, landscape_h)
since you have WHERE user_name = 'constant'

Finally, creator needs creator_ID (already the PRIMARY KEY?)

Starting with `event` avoids the "Using temporary and Filesort" that would be otherwise necessary to achieve the ORDER BY `time`.

If there are other SELECTs, they may or may not be as efficient as I think I have made this query.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.