MySQL Forums
Forum List  »  Performance

Re: Creating Sort Index Slow
Posted by: Rick James
Date: March 17, 2014 10:31PM

In addition to Peter's comments, ...

* Specify latin1 or ascii for fields that don't need utf8.

* Do you have any queries that really use such long indexes?

* Please provide EXPLAIN SELECT ... -- Let's see if we can figure out what it is trying to do.

* Do not specify (m,n) after FLOAT or DOUBLE -- it causes an extra rounding.

* Is PRIMARY KEY (`TYPE`,`REFNO`,`CUSTNO`,`WOS_DATE`) really UNIQUE? (Date/time in a PK is often a mistake.)

* If this is really a time-of-day: `TIME` varchar(8) NOT NULL DEFAULT '', then suggest you use the TIME datatype (3 bytes instead of 26).

* INDEX(type, wos_date, refno, custno) -- may be beneficial. If not, then you may also need to reformulate the query thus:
SELECT  a.*
    FROM  
      ( SELECT  `TYPE`,`REFNO`,`CUSTNO`,`WOS_DATE`
            FROM  artran
            WHERE  type = "SO"
            order by  wos_date desc, refno desc
            limit  20 ) x
    JOIN  artran AS a  USING (`TYPE`,`REFNO`,`CUSTNO`, `WOS_DATE`)
    order by  wos_date desc, refno desc;
In this case, it would do an "index scan" on the much smaller index (much smaller than the data you have). This would run faster, then it would reach into you bulky table 20 times to get the "*".

Options: ReplyQuote


Subject
Views
Written By
Posted
41573
March 14, 2014 09:59PM
15843
March 17, 2014 11:59AM
10817
March 17, 2014 08:20PM
8179
March 17, 2014 08:46PM
Re: Creating Sort Index Slow
6777
March 17, 2014 10:31PM
5370
March 18, 2014 01:13AM
5089
March 19, 2014 04:56PM
4779
March 19, 2014 05:04PM
4073
March 19, 2014 10:06PM
7204
March 19, 2014 11:23PM


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.