Problems with speed using directory plugin

Website: http://www.opticianfinder.co.uk

Hi,

I have built a site for a client using this plugin and added some custom coding.

I am having problems with the return of data in postcode searches for example if I type HA7 as a postcode it takes up to 30 seconds to find a few businesses.

I have made the search absolute as prior it was returning any data that had ha7 in the description so changed that.

Also the client wants SEO friendly URL's and I'm not sure how to set those up.

There are 6752 businesses on the site and the site is hosted in the cloud hosting at Amazon so its a decent size hosting account.

any advice at this point would be most welcome.

Thanks
Paul

  • Modedaweb
    • WPMU DEV Initiate

    • How the postcode and town searches are working, if postcode is entered then below process is execute :

    whenever you search with a particular postcode then below first process is executed:

    (line 10 -lin 18) in that, first charters before space of postcode are taken.
    $radius = 3958.761; // in miles
    $distance=1;
    $flag=0;
    if($_REQUEST['postcode']!='') {
    $postcode_array=explode(' ',trim($_REQUEST['postcode']));
    } else if($_REQUEST['postcode1']!='') {
    $postcode_array=explode(' ',trim($_REQUEST['postcode1']));
    }
    With that half postcode(means with $postcode_array[0]), one query is fire in 'uk_postcodes' table with some calculation to find min and max range of latitude and longitude. (line 19 -lin 36)
    $query = 'SELECT latitude, longitude FROM uk_postcodes WHERE postcode like "'.$postcode_array[0].'%"';
    $result = mysql_query($query);
    $second = mysql_fetch_row($result);
    $checkSecond=mysql_num_rows($result);
    $lat=$second[0]; $lng=$second[1];
    // ensure there were results to calculate with
    if($checkSecond<1){
    $outputResults="Unrecognised postcode entered."; $flag=1;
    }
    // latitude boundaries
    $maxLat = (float) $lat + rad2deg($distance / $radius);
    $minLat = (float) $lat - rad2deg($distance / $radius);
    // longitude boundaries (longitude gets smaller when latitude increases)
    $maxLng = (float) $lng + rad2deg($distance / $radius / cos(deg2rad((float) $lat)));
    $minLng = (float) $lng - rad2deg($distance / $radius / cos(deg2rad((float) $lat)));

    After that postcode array is generated :

    with min and max latitude and longitude range, query is fire in uk_postcodes table to get list of postcodes (before space characters). With those postcodes, second query is fired in wp_postmeta table to find list of full postcodes. So at last within 1 km range postcodes are found. If found postcodes array is blank then dummy two entries are entered in array to show not found error(like 'vvvdvf').
    // get results ordered by distance (approx)
    $qu='SELECT * FROM uk_postcodes WHERE latitude > '.$minLat.' AND latitude < '.$maxLat.' AND longitude > '.$minLng.' AND longitude < '.$maxLng;
    $rs=mysql_query($qu); $ss=array();
    while($result=mysql_fetch_row($rs)) { //echo $result[0].'
    ';
    $postrs1 = mysql_query("select meta_value from wp_postmeta where meta_key LIKE '_ct_text_5238b0c82ca32' AND (meta_value REGEXP '^".$result[0]."[ $]' OR meta_value REGEXP ' ".$result[0]."[ $]')");

    while($result1=mysql_fetch_row($postrs1)) {
    $ss[]=$result1[0];
    }
    }
    if(sizeof($ss)<1 && $_REQUEST['postcode']!='') { $flag=1; $ss[]='vvvdvf'; $ss[]='dsfgddg'; }

    so after that array is pass to wordpress meta query structure.

    I have put comments in meta query structure with which condition/meta query is for which purpose. So you will able to get idea.

    For town or city as per below, town/city value is directly passed to meta query structure.
    if($_REQUEST['town']!='') {
    $search[]= array(
    'key' => '_ct_text_52557cc5a9fa3',
    'value' => $_REQUEST['town']
    );
    }
    if($_REQUEST['city']!='') {
    $search[]= array(
    'key' => '_ct_text_52557ce7220de',
    'value' => $_REQUEST['city']
    );

    • How the coding works around only pulling back the required postcode and those up to 1km away

    As explain above how postcodes within 1km are found. After that array is pass to wordpress meta query structure.

    • Why you cannot alpha sort these results with the data from the postcode search and then those around those as per above see in below code, $ss is postcodes array. Postcodes array is pass with IN clause. So query is execute like this : get one by one each row of table, and compare that in that row postcode is in $ss array or not. So even if we will pass array with ascending alphanumeric order then also fetch rows are not in ascending order of postcode. I have also try to assign 'orderby' array value to 'meta_value' but still its not working.
    $query_args = array(
    'post_type' => 'directory_listing',
    'post_status' => 'publish',
    'paged' => $paged,
    'orderby' => 'title',
    'order' => 'ASC',
    'post__in' => $postcodeids,
    'meta_query' => array(
    'relation' => 'OR',
    array(
    'key' => '_ct_text_5238b0c82ca32',
    'value' => $ss,
    'compare' => 'IN'
    ),
    array(
    'key' => '_ct_text_52557cc5a9fa3',
    'value' => trim($_REQUEST['postcode1'])
    // 'compare' => 'LIKE'
    ),
    array(
    'key' => '_ct_text_52557ce7220de',
    'value' => trim($_REQUEST['postcode1'])
    // 'compare' => 'LIKE'
    )
    )
    );

    • How the data being queried is structured

    see below there are code of meta queries structured :
    /* Wordpress meta query start */
    if($_REQUEST['postcode1']!='') { /* Top Textbox Search */
    $postrs = mysql_query("select post_id from wp_postmeta where (meta_key LIKE '_ct_text_5238b0c82ca32' AND meta_value IN '".$ss."%') OR (meta_key LIKE '_ct_text_52557cc5a9fa3' AND meta_value LIKE '".$_REQUEST['postcode1']."%') OR (meta_key LIKE '_ct_text_52557ce7220de' AND meta_value LIKE '".$_REQUEST['postcode1']."%')");
    while($res=mysql_fetch_array($postrs)) {
    $postcodeids[]=$res[0];
    }
    if(sizeof($ss)>0) {
    $query_args = array(
    'post_type' => 'directory_listing',
    'post_status' => 'publish',
    'paged' => $paged,
    'orderby' => 'title',
    'order' => 'ASC',
    'post__in' => $postcodeids,
    'meta_query' => array(
    'relation' => 'OR',
    array(
    'key' => '_ct_text_5238b0c82ca32',
    'value' => $ss,
    'compare' => 'IN'
    ),
    array(
    'key' => '_ct_text_52557cc5a9fa3',
    'value' => trim($_REQUEST['postcode1'])
    // 'compare' => 'LIKE'
    ),
    array(
    'key' => '_ct_text_52557ce7220de',
    'value' => trim($_REQUEST['postcode1'])
    // 'compare' => 'LIKE'
    )
    )
    );
    } else {
    $query_args = array(
    'post_type' => 'directory_listing',
    'post_status' => 'publish',
    'paged' => $paged,
    'orderby' => 'title',
    'order' => 'ASC',
    'post__in' => $postcodeids,
    'meta_query' => array(
    'relation' => 'OR',
    array(
    'key' => '_ct_text_52557cc5a9fa3',
    'value' => trim($_REQUEST['postcode1'])
    //'compare' => 'LIKE'
    ),
    array(
    'key' => '_ct_text_52557ce7220de',
    'value' => trim($_REQUEST['postcode1'])
    // 'compare' => 'LIKE'
    )
    )
    );
    }
    }
    else if($_REQUEST['list']!='') { /* My Favourites Search */
    $query_args = array(
    'post_type' => 'directory_listing',
    'post_status' => 'publish',
    'orderby' => 'title',
    'order' => 'ASC',
    'post__in' => $includesids
    );
    }
    else if($_REQUEST['post_type']!='' || $_REQUEST['postcode']!='') { /* Left Panel Search */

    if($_REQUEST['costeyetest']!='') {
    $rr=explode('-',$_REQUEST['costeyetest']);
    if($rr[1]!='') {
    $costeyetest=array($rr[0],$rr[1]);
    $typeofeye='BETWEEN';
    } else {
    $costeyetest=$rr[0];
    $typeofeye='>';
    }
    }

    if($_REQUEST['contactlensefitting']!='') {
    $rr=explode('-',$_REQUEST['contactlensefitting']);
    if($rr[1]!='') {
    $costeyetest1=array($rr[0],$rr[1]);
    $typeofeye1='BETWEEN';
    } else {
    $costeyetest1=$rr[0];
    $typeofeye1='>';
    }
    }
    if($_REQUEST['postcode']!='') {
    $search[]= array(
    'key' => '_ct_text_5238b0c82ca32',
    'value' => $ss,
    'compare' => 'IN'
    );
    if($_REQUEST['city']=='' && $flag==1) {
    $search[]= array(
    'key' => '_ct_text_52557ce7220de',
    'value' => 'bdfdg'
    //'compare' => 'LIKE'
    );
    }
    }
    if($_REQUEST['town']!='') {
    $search[]= array(
    'key' => '_ct_text_52557cc5a9fa3',
    'value' => $_REQUEST['town']
    //'compare' => 'LIKE'
    );
    }
    if($_REQUEST['city']!='') {
    $search[]= array(
    'key' => '_ct_text_52557ce7220de',
    'value' => $_REQUEST['city']
    //'compare' => 'LIKE'
    );
    }
    if($_REQUEST['specialist']!='') {
    $search[]=array(
    'key' => '_ct_selectbox_523c4eba3bf35',
    'value' => $_REQUEST['specialist'],
    'compare' => 'LIKE'
    );
    }
    if($_REQUEST['costeyetest']!='') {
    $search[]=array(
    'key' => '_ct_text_5238b232d9b6c',
    'value' => $costeyetest,
    'compare' => $typeofeye,
    'type' => 'numeric'
    );
    }
    if($_REQUEST['contactlensefitting']!='') {
    $search[]=array(
    'key' => '_ct_text_5238b25d42613',
    'value' => $costeyetest1,
    'compare' => $typeofeye1,
    'type' => 'numeric'
    );
    }
    if($_REQUEST['startrating']!='') {
    $search[]=array(
    'key' => '_sr_post_rating',
    'value' => $_REQUEST['startrating']
    );
    }
    if($_REQUEST['brands']!='') {
    $search[]=array(
    'key' => '_ct_text_523b61c2e0094',
    'value' => $_REQUEST['brands'],
    'compare' => 'LIKE'
    );
    }

    $query_args = array(
    'post_type' => 'directory_listing',
    'post_status' => 'publish',
    'paged' => $paged,
    'orderby' => 'title',
    'order' => 'ASC',
    'meta_query' => $search
    );

    } else { /* Default Search */
    $query_args = array(
    'post_type' => 'directory_listing',
    'post_status' => 'publish',
    'orderby' => 'title',
    'order' => 'ASC',
    'paged' => $paged
    );
    }

  • Arnold
    • El Macho WP

    Not at all sure I follow what your doing because the field names are numbers instead of names, so I don't understand why you're comparing against 3 different postcode fields. But...

    In general it's not a good idea to OR a bunch of meta queries together. One huge query is not necessarily faster than several smaller queries combined. I would probably do it by:

    Getting the array of postcodes.

    Sort the array in the order I want the postcodes to be in.

    Loop through the array of postcodes one at a time retrieving the posts that match each

    As each postcode is tried add the results to the collection of posts.

    If you ne4ed to do something like select by postcode and then sort by Title for example, do the same and just save the ID of each post. Tehn make a final query of ID IN your collection orderby Title.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.