MySQL Forums
Forum List  »  Performance

Re: speed up query
Posted by: Rick James
Date: May 01, 2010 02:20PM

select  count(snrdown)
    from  dsl_data,dslam_ports
    where  dslam_ports.dslam_id='$dslamid'
      and  dsl_data.port_id=dslam_ports.id
      and  dsl_data.snrdown > 399

How many rows of dslam_ports have dslam_id='$dslamid' ?
How many rows of dsl_data have snrdown > 399 ?
Assuming these exist:
INDEX(dslam_id)
INDEX(snrdown)
then the optimizer will start with whichever table has the smaller number of rows. But then it has to reach into the other table via port_id to filter on the other restriction. Not cheap. Not fast.

If dslam_id and snrdown were in the same table, that this index would make it run much faster:
INDEX(dslam_id, snrdown)

A summary table keyed by dslam_id and, say, snrdown DIV 100 might cover a bunch of queries (snrdown > 299, snrdown > 599, srndown < 400, etc; but not snrdown > 350)

Options: ReplyQuote


Subject
Views
Written By
Posted
4318
April 18, 2010 06:42AM
1606
April 18, 2010 04:04PM
1670
April 18, 2010 05:40PM
1411
April 27, 2010 02:29PM
1539
April 27, 2010 07:59PM
1405
April 28, 2010 12:12AM
1666
April 28, 2010 06:22AM
1609
April 29, 2010 01:03AM
1462
April 29, 2010 02:17AM
1484
April 29, 2010 08:19AM
1491
April 29, 2010 09:18AM
1394
April 30, 2010 12:30AM
1651
April 30, 2010 07:47AM
1572
April 30, 2010 08:21AM
1495
April 30, 2010 10:08AM
1404
May 01, 2010 02:34AM
1426
May 01, 2010 09:54AM
1371
May 01, 2010 10:48AM
Re: speed up query
1505
May 01, 2010 02:20PM
1448
May 02, 2010 03:54AM


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.