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
4328
April 18, 2010 06:42AM
1609
April 18, 2010 04:04PM
1673
April 18, 2010 05:40PM
1415
April 27, 2010 02:29PM
1547
April 27, 2010 07:59PM
1410
April 28, 2010 12:12AM
1675
April 28, 2010 06:22AM
1615
April 29, 2010 01:03AM
1471
April 29, 2010 02:17AM
1489
April 29, 2010 08:19AM
1496
April 29, 2010 09:18AM
1399
April 30, 2010 12:30AM
1654
April 30, 2010 07:47AM
1580
April 30, 2010 08:21AM
1500
April 30, 2010 10:08AM
1411
May 01, 2010 02:34AM
1430
May 01, 2010 09:54AM
1375
May 01, 2010 10:48AM
Re: speed up query
1512
May 01, 2010 02:20PM
1454
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.