Re: Query time difference
Posted by:
Rick James
Date: April 27, 2015 02:39PM
First, let's improve the performance in a few minor ways...
* Use either deleted = 0 or deleted IS NULL, not both for that flag. (With that change, we can add it to a composite index.
* Add INDEX(service_provider_id, notify_on). If you have changed `deleted`, then INDEX(service_provider_id, deleted, notify_on)
* BIGINT takes 8 bytes. INT UNSIGNED takes 4 bytes. Do you really need more than 4 billion ids?
* Why are you PARTITIONing? It provides no intrinsic performance boost. (But there may be purging or some SELECTs that benefit from it.)
Which 3rd party software is generating these queries?
Please provide EXPLAIN PARTITIONS SELECT ... for your query (before and after any changes you make; 50:20 and 50:21).
Was the 50:21 spanning a month boundary?
I think (but am not sure) that if you do both the `deleted` change and have the 3-column index, it will be always fast.
Subject
Views
Written By
Posted
1723
April 26, 2015 07:28AM
Re: Query time difference
647
April 27, 2015 02:39PM
685
April 27, 2015 11:29PM
658
April 29, 2015 05:50PM
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.