List:General Discussion« Previous MessageNext Message »
From:Mihail Manolov Date:September 8 2011 9:21pm
Subject:Re: Query Optimization
View as plain text  
From the manual: "The default behavior for UNION is that duplicate rows are removed from
the result."

On Sep 8, 2011, at 4:50 PM, Brandon Phelps wrote:

> Mihail,
> 
> Thanks so much!  I modified your example to include the proper ORDER BY and LIMIT
> clauses and this, so far, is running super fast (0.0007 seconds).  Question, if a record's
> open_dt is between the range AND the close_dt is between the range as well, will the UNION
> output the record twice?  If so, is there any way to prevent that?
> 
> (SELECT
> 	sc.open_dt,
> 	sc.close_dt,
> 	sc.protocol,
> 	INET_NTOA(sc.src_address) AS src_address,
> 	sc.src_port,
> 	INET_NTOA(sc.dst_address) AS dst_address,
> 	sc.dst_port,
> 	sc.sent,
> 	sc.rcvd,
> 	spm.desc AS src_port_desc,
> 	dpm.desc AS dst_port_desc
> FROM sonicwall_connections AS sc
> 	LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
> 	LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
> WHERE open_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58')
> UNION
> (SELECT
> 	sc.open_dt,
> 	sc.close_dt,
> 	sc.protocol,
> 	INET_NTOA(sc.src_address) AS src_address,
> 	sc.src_port,
> 	INET_NTOA(sc.dst_address) AS dst_address,
> 	sc.dst_port,
> 	sc.sent,
> 	sc.rcvd,
> 	spm.desc AS src_port_desc,
> 	dpm.desc AS dst_port_desc
> FROM sonicwall_connections AS sc
> 	LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
> 	LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
> WHERE close_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58')
> ORDER BY rcvd DESC LIMIT 10;
> 
>
> +----+--------------+------------+--------+--------------------------------------------+--------------+---------+--------------------+------+----------------+
> | id | select_type  | table      | type   | possible_keys                            
>  | key          | key_len | ref                | rows | Extra          |
>
> +----+--------------+------------+--------+--------------------------------------------+--------------+---------+--------------------+------+----------------+
> |  1 | PRIMARY      | sc         | range  |
> open_dt,ndx_open_close_rcvd,ndx_open_close | open_dt      | 8       | NULL               |
> 1057 | Using where    |
> |  1 | PRIMARY      | spm        | eq_ref | PRIMARY                                  
>  | PRIMARY      | 2       | syslog.sc.src_port |    1 |                |
> |  1 | PRIMARY      | dpm        | eq_ref | PRIMARY                                  
>  | PRIMARY      | 2       | syslog.sc.dst_port |    1 |                |
> |  2 | UNION        | sc         | range  | ndx_close_dt                             
>  | ndx_close_dt | 8       | NULL               | 1131 | Using where    |
> |  2 | UNION        | spm        | eq_ref | PRIMARY                                  
>  | PRIMARY      | 2       | syslog.sc.src_port |    1 |                |
> |  2 | UNION        | dpm        | eq_ref | PRIMARY                                  
>  | PRIMARY      | 2       | syslog.sc.dst_port |    1 |                |
> | NULL | UNION RESULT | <union1,2> | ALL    | NULL                             
>          | NULL         | NULL    | NULL               | NULL | Using filesort |
>
> +----+--------------+------------+--------+--------------------------------------------+--------------+---------+--------------------+------+----------------+
> 
> 
> 
> On 09/08/2011 03:45 PM, Mihail Manolov wrote:
>> How about:
>> 
>> SELECT
>> 	sc.open_dt,
>> 	sc.close_dt,
>> 	sc.protocol,
>> 	INET_NTOA(sc.src_address) AS src_address,
>> 	sc.src_port,
>> 	INET_NTOA(sc.dst_address) AS dst_address,
>> 	sc.dst_port,
>> 	sc.sent,
>> 	sc.rcvd,
>> 	spm.desc AS src_port_desc,
>> 	dpm.desc AS dst_port_desc
>> FROM sonicwall_connections AS sc
>> 	LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
>> 	LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
>> WHERE open_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58'
>> 
>> UNION
>> 
>> SELECT
>> 	sc.open_dt,
>> 	sc.close_dt,
>> 	sc.protocol,
>> 	INET_NTOA(sc.src_address) AS src_address,
>> 	sc.src_port,
>> 	INET_NTOA(sc.dst_address) AS dst_address,
>> 	sc.dst_port,
>> 	sc.sent,
>> 	sc.rcvd,
>> 	spm.desc AS src_port_desc,
>> 	dpm.desc AS dst_port_desc
>> FROM sonicwall_connections AS sc
>> 	LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
>> 	LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
>> WHERE close_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58'
>> 
>> 
>> 
>> On Sep 8, 2011, at 3:25 PM, Brandon Phelps wrote:
>> 
>>> Thanks for the idea Derek, however given the following query my EXPLAIN
> output is identical:
>>> 
>>> SELECT
>>> 	sc.open_dt,
>>> 	sc.close_dt,
>>> 	sc.protocol,
>>> 	INET_NTOA(sc.src_address) AS src_address,
>>> 	sc.src_port,
>>> 	INET_NTOA(sc.dst_address) AS dst_address,
>>> 	sc.dst_port,
>>> 	sc.sent,
>>> 	sc.rcvd,
>>> 	spm.desc AS src_port_desc,
>>> 	dpm.desc AS dst_port_desc
>>> FROM sonicwall_connections AS sc
>>> 	LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
>>> 	LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
>>> WHERE ('2011-09-07 13:18:58'<= open_dt<= '2011-09-08 13:18:58') OR
> ('2011-09-07 13:18:58'<= close_dt<= '2011-09-08 13:18:58');
>>> 
>>> 
>>>
> +----+-------------+-------+--------+---------------+---------+---------+--------------------+----------+-------------+
>>> | id | select_type | table | type   | possible_keys | key     | key_len | ref
>                | rows     | Extra       |
>>>
> +----+-------------+-------+--------+---------------+---------+---------+--------------------+----------+-------------+
>>> |  1 | SIMPLE      | sc    | ALL    | NULL          | NULL    | NULL    |
> NULL               | 32393330 | Using where |
>>> |  1 | SIMPLE      | spm   | eq_ref | PRIMARY       | PRIMARY | 2       |
> syslog.sc.src_port |        1 |             |
>>> |  1 | SIMPLE      | dpm   | eq_ref | PRIMARY       | PRIMARY | 2       |
> syslog.sc.dst_port |        1 |             |
>>>
> +----+-------------+-------+--------+---------------+---------+---------+--------------------+----------+-------------+
>>> 
>>> I did create indexes on open_dt and close_dt (2 separate indexes).
>>> 
>>> 
>>> 
>>> On 09/08/2011 02:55 PM, Derek Downey wrote:
>>>> Correct me if I'm wrong. You're wanting to get all records that have an
> open_date or a close_date between two times.
>>>> 
>>>> If that's correct, you might be able to get an index_merge by doing a
> query like:
>>>> 
>>>> WHERE ((starting time)<=open_dt<= (ending time)) OR ((starting
> time)<=close_dt<=(ending time))
>>>> 
>>>> and creating two indexes (one on 'open_dt' and the other on 'close_dt')
>>>> 
>>>> http://dev.mysql.com/doc/refman/5.1/en/index-merge-optimization.html
>>>> 
>>>> Regards,
>>>> Derek
>>>> 
>>>> On Sep 8, 2011, at 2:50 PM, Brandon Phelps wrote:
>>>> 
>>>>> Andy,
>>>>> 
>>>>> The queries take minutes to run.  MySQL is 5.1.54 and it's running on
> Ubuntu server 11.04.  Unfortunately the machine only has 2GB of RAM but no other major
> daemons are running on the machine.  We are running RAID 1 (mirroring) with 1TB drives. 
> The tables in question here are all MyISAM.  When running with the LIMIT 10 my EXPLAIN
> is:
>>>>> 
>>>>>
> +----+-------------+-------+--------+---------------------+---------------------+---------+--------------------+----------+-----------------------------+
>>>>> | id | select_type | table | type   | possible_keys       | key      
>           | key_len | ref                | rows     | Extra                       |
>>>>>
> +----+-------------+-------+--------+---------------------+---------------------+---------+--------------------+----------+-----------------------------+
>>>>> |  1 | SIMPLE      | sc    | range  | ndx_open_close_rcvd |
> ndx_open_close_rcvd | 8       | NULL               | 32393316 | Using where; Using
> filesort |
>>>>> |  1 | SIMPLE      | spm   | eq_ref | PRIMARY             | PRIMARY  
>           | 2       | syslog.sc.src_port |        1 |                             |
>>>>> |  1 | SIMPLE      | dpm   | eq_ref | PRIMARY             | PRIMARY  
>           | 2       | syslog.sc.dst_port |        1 |                             |
>>>>>
> +----+-------------+-------+--------+---------------------+---------------------+---------+--------------------+----------+-----------------------------+
>>>>> 
>>>>> When I remove the LIMIT 10 I get:
>>>>> 
>>>>>
> ----+-------------+-------+--------+---------------------+---------+---------+--------------------+----------+-----------------------------+
>>>>> | id | select_type | table | type   | possible_keys       | key     |
> key_len | ref                | rows     | Extra                       |
>>>>>
> +----+-------------+-------+--------+---------------------+---------+---------+--------------------+----------+-----------------------------+
>>>>> |  1 | SIMPLE      | sc    | ALL    | ndx_open_close_rcvd | NULL    |
> NULL    | NULL               | 32393330 | Using where; Using filesort |
>>>>> |  1 | SIMPLE      | spm   | eq_ref | PRIMARY             | PRIMARY |
> 2       | syslog.sc.src_port |        1 |                             |
>>>>> |  1 | SIMPLE      | dpm   | eq_ref | PRIMARY             | PRIMARY |
> 2       | syslog.sc.dst_port |        1 |                             |
>>>>>
> +----+-------------+-------+--------+---------------------+---------+---------+--------------------+----------+-----------------------------+
>>>>> 
>>>>> Thanks for all your help thus far.
>>>>> 
>>>>> On 09/08/2011 02:38 PM, Andrew Moore wrote:
>>>>>> I don't think I saw any query timings in the emails (maybe I
> missed them).
>>>>>> 
>>>>>> What version of MySQL are you currently using?
>>>>>> What does the explain look like when your remove the limit 10?
>>>>>> Is your server tuned for MyISAM or InnoDB?
>>>>>> What kind of disk setup is in use?
>>>>>> How much memory is in your machine?
>>>>>> 
>>>>>> 
>>>>>> On Thu, Sep 8, 2011 at 7:27 PM, Brandon
> Phelps<bphelps@stripped>    wrote:
>>>>>> 
>>>>>>> Thanks for the reply Andy.  Unfortunately the users will be
> selecting
>>>>>>> varying date ranges and new data is constantly coming in, so
> I am not sure
>>>>>>> how I could archive/cache the necessary data that would be
> any more
>>>>>>> efficient than simply using the database directly.
>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> On 09/08/2011 02:16 PM, Andrew Moore wrote:
>>>>>>> 
>>>>>>>> Thinking outside the query, is there any archiving that
> could happen to
>>>>>>>> make
>>>>>>>> your large tables kinder in the range scan?
>>>>>>>> 
>>>>>>>> Andy
>>>>>>>> 
>>>>>>>> On Thu, Sep 8, 2011 at 7:03 PM, Brandon
> Phelps<bphelps@stripped>     wrote:
>>>>>>>> 
>>>>>>>>  On 09/01/2011 01:32 PM, Brandon Phelps wrote:
>>>>>>>>> 
>>>>>>>>>  On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote:
>>>>>>>>>> 
>>>>>>>>>>  On 9/1/2011 09:42, Brandon Phelps wrote:
>>>>>>>>>>> 
>>>>>>>>>>>  On 09/01/2011 04:59 AM, Jochem van Dieten
> wrote:
>>>>>>>>>>>> 
>>>>>>>>>>>>> ...
>>>>>>>>>>>>>> 
>>>>>>>>>>>>> 
>>>>>>>>>>>>  WHERE
>>>>>>>>>>>>>> (open_dt>= '2011-08-30
> 00:00:00' OR close_dt>= '2011-08-30
>>>>>>>>>>>>>> 
>>>>>>>>>>>>> 00:00:00')
>>>>>>>>>>>> 
>>>>>>>>>>>>> AND (open_dt<= '2011-08-30
> 12:36:53' OR close_dt<= '2011-08-30
>>>>>>>>>>>>>> 
>>>>>>>>>>>>> 12:36:53')
>>>>>>>>>>>> 
>>>>>>>>>>>>  In that case your logic here simplifies
> to:
>>>>>>>>>>>>> WHERE
>>>>>>>>>>>>> open_dt>= '2011-08-30 00:00:00'
>>>>>>>>>>>>> AND
>>>>>>>>>>>>> close_dt<= '2011-08-30 12:36:53'
>>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>>  Now add an index over open_dt and
> close_dt and see what happens.
>>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>>  Jochem
>>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>> Jochem,
>>>>>>>>>>>> 
>>>>>>>>>>>> I can't really use your WHERE logic
> because I also need to retrieve
>>>>>>>>>>>> results where the open_dt time is out of
> the range specified. For
>>>>>>>>>>>> example, a very large file download might
> span multiple days so given
>>>>>>>>>>>> your logic if the connection was started
> 2 days ago and I want to pull
>>>>>>>>>>>> 1
>>>>>>>>>>>> days worth of connections, I would miss
> that entry. Basically I want
>>>>>>>>>>>> to
>>>>>>>>>>>> SELECT all of the records that were
> opened OR closed during the
>>>>>>>>>>>> specified time period, ie. if any
> activity happened between my start
>>>>>>>>>>>> and
>>>>>>>>>>>> end dates, I need to see that record.
>>>>>>>>>>>> 
>>>>>>>>>>>> Any other ideas?
>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>>>  I believe Jochem was on the right track
> but he got his dates
>>>>>>>>>>> reversed.
>>>>>>>>>>> 
>>>>>>>>>>> Let's try a little ASCII art to show the
> situation. I will setup a
>>>>>>>>>>> query
>>>>>>>>>>> window with two markers (s) and (e). Events
> will be marked by |----|
>>>>>>>>>>> markers
>>>>>>>>>>> showing their durations.
>>>>>>>>>>> 
>>>>>>>>>>> a) (s) (e)
>>>>>>>>>>> b) |---|
>>>>>>>>>>> c) |---|
>>>>>>>>>>> d) |---|
>>>>>>>>>>> e) |--------------------|
>>>>>>>>>>> f) |---|
>>>>>>>>>>> g) |---|
>>>>>>>>>>> 
>>>>>>>>>>> To describe these situations:
>>>>>>>>>>> a) is the window for which you want to query
> (s) is the starting time
>>>>>>>>>>> and
>>>>>>>>>>> (e) is the ending time for the date range you
> are interested in.
>>>>>>>>>>> b) the event starts and stops before your
> window exists. It won't be
>>>>>>>>>>> part
>>>>>>>>>>> of your results.
>>>>>>>>>>> c) the event starts before the window but
> ends within the window -
>>>>>>>>>>> include this
>>>>>>>>>>> d) the event starts and ends within the
> window - include this
>>>>>>>>>>> e) the event starts before the window and
> ends after the window -
>>>>>>>>>>> include
>>>>>>>>>>> this
>>>>>>>>>>> f) the event starts inside the window but
> ends beyond the window -
>>>>>>>>>>> include this.
>>>>>>>>>>> g) the event starts and ends beyond the
> window - exclude this.
>>>>>>>>>>> 
>>>>>>>>>>> In order to get every event in the range of
> c-f, here is what you need
>>>>>>>>>>> for a WHERE clause
>>>>>>>>>>> 
>>>>>>>>>>> WHERE start<= (ending time) and end>=
> (starting time)
>>>>>>>>>>> 
>>>>>>>>>>> Try that and let us know the results.
>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>> Thanks Jochem and Shawn, however the following
> two queries result in the
>>>>>>>>>> exact same EXPLAIN output: (I hope the tables
> don't wrap too early for
>>>>>>>>>> you)
>>>>>>>>>> 
>>>>>>>>>> Old method:
>>>>>>>>>> SELECT
>>>>>>>>>> sc.open_dt,
>>>>>>>>>> sc.close_dt,
>>>>>>>>>> sc.protocol,
>>>>>>>>>> INET_NTOA(sc.src_address) AS src_address,
>>>>>>>>>> sc.src_port,
>>>>>>>>>> INET_NTOA(sc.dst_address) AS dst_address,
>>>>>>>>>> sc.dst_port,
>>>>>>>>>> sc.sent,
>>>>>>>>>> sc.rcvd,
>>>>>>>>>> spm.desc AS src_port_desc,
>>>>>>>>>> dpm.desc AS dst_port_desc
>>>>>>>>>> FROM firewall_connections AS sc
>>>>>>>>>> LEFT JOIN port_mappings AS spm ON spm.port =
> sc.src_port
>>>>>>>>>> LEFT JOIN port_mappings AS dpm ON dpm.port =
> sc.dst_port
>>>>>>>>>> WHERE
>>>>>>>>>> (open_dt>= '2011-08-31 09:53:31' OR
> close_dt>= '2011-08-31 09:53:31')
>>>>>>>>>> AND (open_dt<= '2011-09-01 09:53:31' OR
> close_dt<= '2011-09-01
>>>>>>>>>> 09:53:31')
>>>>>>>>>> ORDER BY rcvd DESC
>>>>>>>>>> LIMIT 0, 10;
>>>>>>>>>> 
>>>>>>>>>> New method with BTREE index on open_dt, close_dt
> (index name is
>>>>>>>>>> ndx_open_close_dt):
>>>>>>>>>> SELECT
>>>>>>>>>> sc.open_dt,
>>>>>>>>>> sc.close_dt,
>>>>>>>>>> sc.protocol,
>>>>>>>>>> INET_NTOA(sc.src_address) AS src_address,
>>>>>>>>>> sc.src_port,
>>>>>>>>>> INET_NTOA(sc.dst_address) AS dst_address,
>>>>>>>>>> sc.dst_port,
>>>>>>>>>> sc.sent,
>>>>>>>>>> sc.rcvd,
>>>>>>>>>> spm.desc AS src_port_desc,
>>>>>>>>>> dpm.desc AS dst_port_desc
>>>>>>>>>> FROM firewall_connections AS sc
>>>>>>>>>> LEFT JOIN port_mappings AS spm ON spm.port =
> sc.src_port
>>>>>>>>>> LEFT JOIN port_mappings AS dpm ON dpm.port =
> sc.dst_port
>>>>>>>>>> WHERE
>>>>>>>>>> open_dt<= '2011-09-01 09:53:31' AND
> close_dt>= '2011-08-31 09:53:31'
>>>>>>>>>> ORDER BY rcvd DESC
>>>>>>>>>> LIMIT 0, 10;
>>>>>>>>>> 
>>>>>>>>>> EXPLAIN output for old method:
>>>>>>>>>>
> +----+-------------+-------+--****------+---------------------**--**
>>>>>>>>>>
> ----+----------+---------+----****----------------+------+----**
>>>>>>>>>> --**-------+
>>>>>>>>>> | id | select_type | table | type | possible_keys
> | key | key_len | ref
>>>>>>>>>> |
>>>>>>>>>> rows | Extra |
>>>>>>>>>>
> +----+-------------+-------+--****------+---------------------**--**
>>>>>>>>>>
> ----+----------+---------+----****----------------+------+----**
>>>>>>>>>> --**-------+
>>>>>>>>>> | 1 | SIMPLE | sc | index |
> open_dt,ndx_open_close_dt | ndx_rcvd | 4 |
>>>>>>>>>> NULL | 10 | Using where |
>>>>>>>>>> | 1 | SIMPLE | spm | eq_ref | PRIMARY | PRIMARY |
> 2 | syslog.sc.src_port
>>>>>>>>>> |
>>>>>>>>>> 1 | |
>>>>>>>>>> | 1 | SIMPLE | dpm | eq_ref | PRIMARY | PRIMARY |
> 2 | syslog.sc.dst_port
>>>>>>>>>> |
>>>>>>>>>> 1 | |
>>>>>>>>>>
> +----+-------------+-------+--****------+---------------------**--**
>>>>>>>>>>
> ----+----------+---------+----****----------------+------+----**
>>>>>>>>>> --**-------+
>>>>>>>>>> 
>>>>>>>>>> EXPLAIN output for new method with new index:
>>>>>>>>>>
> +----+-------------+-------+--****------+---------------------**--**
>>>>>>>>>>
> ----+----------+---------+----****----------------+------+----**
>>>>>>>>>> --**-------+
>>>>>>>>>> | id | select_type | table | type | possible_keys
> | key | key_len | ref
>>>>>>>>>> |
>>>>>>>>>> rows | Extra |
>>>>>>>>>>
> +----+-------------+-------+--****------+---------------------**--**
>>>>>>>>>>
> ----+----------+---------+----****----------------+------+----**
>>>>>>>>>> --**-------+
>>>>>>>>>> | 1 | SIMPLE | sc | index |
> open_dt,ndx_open_close_dt | ndx_rcvd | 4 |
>>>>>>>>>> NULL | 10 | Using where |
>>>>>>>>>> | 1 | SIMPLE | spm | eq_ref | PRIMARY | PRIMARY |
> 2 | syslog.sc.src_port
>>>>>>>>>> |
>>>>>>>>>> 1 | |
>>>>>>>>>> | 1 | SIMPLE | dpm | eq_ref | PRIMARY | PRIMARY |
> 2 | syslog.sc.dst_port
>>>>>>>>>> |
>>>>>>>>>> 1 | |
>>>>>>>>>>
> +----+-------------+-------+--****------+---------------------**--**
>>>>>>>>>>
> ----+----------+---------+----****----------------+------+----**
>>>>>>>>>> --**-------+
>>>>>>>>>> 
>>>>>>>>>> SHOW INDEX:
>>>>>>>>>>
> +----------------------+------****------+-------------------+-**--**
>>>>>>>>>>
> -----------+-------------+----****-------+-------------+------**--**
>>>>>>>>>> --+--------+------+-----------****-+---------+
>>>>>>>>>> | Table | Non_unique | Key_name | Seq_in_index |
> Column_name | Collation
>>>>>>>>>> |
>>>>>>>>>> Cardinality | Sub_part | Packed | Null |
> Index_type | Comment |
>>>>>>>>>>
> +----------------------+------****------+-------------------+-**--**
>>>>>>>>>>
> -----------+-------------+----****-------+-------------+------**--**
>>>>>>>>>> --+--------+------+-----------****-+---------+
>>>>>>>>>> | firewall_connections | 1 | ndx_open_close_dt |
> 1 | open_dt | A |
>>>>>>>>>> 1342691
>>>>>>>>>> | NULL | NULL | | BTREE | |
>>>>>>>>>> | firewall_connections | 1 | ndx_open_close_dt |
> 2 | close_dt | A |
>>>>>>>>>> 6377783 | NULL | NULL | | BTREE | |
>>>>>>>>>>
> +----------------------+------****------+-------------------+-**--**
>>>>>>>>>>
> -----------+-------------+----****-------+-------------+------**--**
>>>>>>>>>> --+--------+------+-----------****-+---------+
>>>>>>>>>> 
>>>>>>>>>> 
>>>>>>>>>> Although right now the queries do seem to be
> executing much faster,
>>>>>>>>>> although I'm not quite sure why. And I'm not sure
> why the new
>>>>>>>>>> ndx_open_close_dt isn't being used either.
>>>>>>>>>> 
>>>>>>>>>> -Brandon
>>>>>>>>>> 
>>>>>>>>>> 
>>>>>>>>>>  I am still having a big issue with my query as
> seen above.  The table
>>>>>>>>> is up
>>>>>>>>> to around 32 million records at the moment and either
> of the two SELECT
>>>>>>>>> queries above take a very long time to run.  Is there
> anything at all I
>>>>>>>>> can
>>>>>>>>> do to speed things up?  It seems that changing the
> format of the WHERE
>>>>>>>>> clause did not help at all, as the EXPLAIN output is
> exactly the same for
>>>>>>>>> both version.  I also tried adding an index on
> (open_dt, close_dt, rcvd)
>>>>>>>>> but
>>>>>>>>> that index does not get used.
>>>>>>>>> 
>>>>>>>>> Any other ideas?
>>>>>>>>> 
>>>>>>>>> Thanks in advance,
>>>>>>>>> 
>>>>>>>>> 
>>>>>>>>> Brandon
>>>>>>>>> 
>>>>>>>>> --
>>>>>>>>> MySQL General Mailing List
>>>>>>>>> For list archives: http://lists.mysql.com/mysql
>>>>>>>>> To unsubscribe:   
> http://lists.mysql.com/mysql?****<http://lists.mysql.com/mysql?**>
>>>>>>>>>
> unsub=eroomydna@stripped<http**://lists.mysql.com/mysql?**
>>>>>>>>>
> unsub=eroomydna@stripped<http://lists.mysql.com/mysql?unsub=1
>>>>>>>>>> 
>>>>>>>>> 
>>>>>>>>> 
>>>>>>>>> 
>>>>>>>> 
>>>>>>> --
>>>>>>> MySQL General Mailing List
>>>>>>> For list archives: http://lists.mysql.com/mysql
>>>>>>> To unsubscribe:    http://lists.mysql.com/mysql?**
>>>>>>>
> unsub=eroomydna@stripped<http://lists.mysql.com/mysql?unsub=1
>>>>>>> 
>>>>>>> 
>>>>>> 
>>>>> 
>>>>> --
>>>>> MySQL General Mailing List
>>>>> For list archives: http://lists.mysql.com/mysql
>>>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>>>> 
>>>> 
>>>> 
>>> 
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>> 
>> 
>> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

Thread
Query OptimizationBrandon Phelps30 Aug
  • Re: Query OptimizationMichael Dykman30 Aug
  • Re: Query OptimizationJochem van Dieten1 Sep
    • Re: Query OptimizationBrandon Phelps1 Sep
      • Re: Query OptimizationMySQL)1 Sep
        • Re: Query OptimizationBrandon Phelps1 Sep
          • Re: Query OptimizationBrandon Phelps8 Sep
            • Re: Query OptimizationAndrew Moore8 Sep
              • Re: Query OptimizationBrandon Phelps8 Sep
                • Re: Query OptimizationAndrew Moore8 Sep
                  • Re: Query OptimizationBrandon Phelps8 Sep
                    • Re: Query OptimizationDerek Downey8 Sep
                      • Re: Query OptimizationBrandon Phelps8 Sep
                        • Re: Query OptimizationMihail Manolov8 Sep
                          • Re: Query OptimizationBrandon Phelps8 Sep
                            • Re: Query OptimizationMihail Manolov8 Sep
                              • Re: Query OptimizationBrandon Phelps8 Sep
                • Re: Query OptimizationMihail Manolov8 Sep
                  • Re: Query OptimizationBrandon Phelps8 Sep
                    • Re: Query OptimizationAndrew Moore8 Sep
                      • Re: Query OptimizationBrandon Phelps8 Sep