Dating Website Support Forum - Webscribble

when my users select to search for members with pictures, the site loads very very slow......17 seconds delay and its a dedicated server......what can I do to fix this issue?

It sounds like your database has not been indexed.

I had this issue before indexing my database

now loads fine even with 20,000 members

the file I have for indexing the database works for 3.0 .. not 100 percent sure if it works for 2.0

PM me and I will provide you with the indexing script.

Paul

you might find this funny, but Im using webdate 2 files on the webdate 3 database, it works fine

what do you mean by Indexing ??? how dose that work ???

create a phpfile called dbindex.php

add this code into the file

Code: Select all

<?
   include "engine/load_configuration.php";
   
   if (!mysql_connect($db_host, $db_login, $db_pswd))
   {
      echo 'Could not connect to mysql';
      exit;
   }

   $r = mysql_list_tables($db_name);

   if (!$r)
   {
      echo "DB Error, could not list tables\n";
      echo 'MySQL Error: ' . mysql_error();
      exit;
   }

   while ($f = mysql_fetch_row($r))
   {
       if(ereg("dt_", $f[0]) || ereg("webDate", $f[0]))
      {

         echo "Table: $f[0]\n<br>";

         $rFields = mysql_query("SHOW COLUMNS FROM ".$f[0]);
         if (!$rFields)
         {
            echo 'Could not run query: ' . mysql_error();
            exit;
         }
         if (mysql_num_rows($rFields) > 0)
         {
            while ($fField = mysql_fetch_row($rFields))
            {
                mysql_query("ALTER TABLE `$f[0]` DROP INDEX `$fField[0]`");
               if($fField[0] != "id")
               {
                   echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Field: $fField[0] - ";
                  $rIndex = mysql_query("ALTER TABLE `$f[0]` ADD INDEX ( `$fField[0]` )");

                  if(!$rIndex)
                     echo 'Could not run query: ' . mysql_error() . '<br>';
                  else
                      echo 'INDEXED<br>';
               }
            }
         }
      }
      else continue;
   }
?>


upload this file to your root directory...

now direct your browser to the file http://www.yoursite.com/dbindex.php

it will run the database index process (this only needs to be run once so after it is finished delete the file)

let me know how this works for you

Paul

exactly what is it going to do to the database ??? is it going to erase stuff ???

it does not erase anything it simply indexes each field so it can locate matches faster when you perform a search

are you sure its not going to mess anything up ??? <: and how often should I run this script ???

as I said in the previous post... run this only once then delete... it is a one time thing.

go to phpmyadmin make a backup of your database.

run the script... should fix the issues.. this will not erase anything it only ads index to the existing rows

Paul

Hi,

I had the same problem when I transfered my database in the past and Paul mentioned about Indexing. I then looked at the original tables.sql setup file provided by Webscribble and found at the end a bunch of entries that are supposed to create indexes. I applied these to my database and all works fine now. I guess, this could be another way of coping with your issue. You can find the entries in your tables.sql file, or if you don't have it, here they are:

Note: This is for Webdate 3 database

Code: Select all

ALTER TABLE `dt_address_book` ADD INDEX(`member_id`);
ALTER TABLE `dt_address_book` ADD INDEX(`contact_profile_id`);
ALTER TABLE `dt_badwords` ADD INDEX(`name`);
ALTER TABLE `dt_badwords` ADD INDEX(`relative_order`);
ALTER TABLE `dt_banned_ips` ADD INDEX(`ip_addr`);
ALTER TABLE `dt_banned_ips` ADD INDEX(`ban_end_time`);
ALTER TABLE `dt_banned_ips` ADD INDEX(`name`);
ALTER TABLE `dt_banned_ips` ADD INDEX(`type`);
ALTER TABLE `dt_billing_history` ADD INDEX(`member_id`);
ALTER TABLE `dt_billing_history` ADD INDEX(`order_id`);
ALTER TABLE `dt_billing_history` ADD INDEX(`order_date`);
ALTER TABLE `dt_billing_history` ADD INDEX(`amount`);
ALTER TABLE `dt_billing_history` ADD INDEX(`description`);
ALTER TABLE `dt_billing_history` ADD INDEX(`type`);
ALTER TABLE `dt_blocked` ADD INDEX(`member_id`);
ALTER TABLE `dt_blocked` ADD INDEX(`blocked_id`);
ALTER TABLE `dt_blog_comments` ADD INDEX(`blog_entry_id`);
ALTER TABLE `dt_blog_comments` ADD INDEX(`date`);
ALTER TABLE `dt_blog_comments` ADD INDEX(`member_login`);
ALTER TABLE `dt_blog_comments` ADD INDEX(`member_id`);
ALTER TABLE `dt_blog_entries` ADD INDEX(`blog_id`);
ALTER TABLE `dt_blog_entries` ADD INDEX(`blog_entry_title`);
ALTER TABLE `dt_blog_entries` ADD INDEX(`date`);
ALTER TABLE `dt_blog_entries` ADD INDEX(`is_edited`);
ALTER TABLE `dt_blog_entries` ADD INDEX(`edit_date`);
ALTER TABLE `dt_blog_settings` ADD INDEX(`blog_id`);
ALTER TABLE `dt_blog_settings` ADD INDEX(`comments_enabled`);
ALTER TABLE `dt_blog_settings` ADD INDEX(`blog_title`);
ALTER TABLE `dt_blog_settings` ADD INDEX(`who_can_comment`);
ALTER TABLE `dt_blog_settings` ADD INDEX(`email_notification`);
ALTER TABLE `dt_blog_settings` ADD INDEX(`show_profile_images`);
ALTER TABLE `dt_blog_settings` ADD INDEX(`entries_per_page`);
ALTER TABLE `dt_blogs` ADD INDEX(`member_login`);
ALTER TABLE `dt_blogs` ADD INDEX(`member_id`);
ALTER TABLE `dt_body_types` ADD INDEX(`name`);
ALTER TABLE `dt_body_types` ADD INDEX(`relative_order`);
ALTER TABLE `dt_charge` ADD INDEX(`amount`);
ALTER TABLE `dt_charge` ADD INDEX(`name`);
ALTER TABLE `dt_charge` ADD INDEX(`type`);
ALTER TABLE `dt_charge` ADD INDEX(`quantity`);
ALTER TABLE `dt_charge` ADD INDEX(`relative_order`);
ALTER TABLE `dt_countries` ADD INDEX(`name`);
ALTER TABLE `dt_countries` ADD INDEX(`relative_order`);
ALTER TABLE `dt_drinking` ADD INDEX(`name`);
ALTER TABLE `dt_drinking` ADD INDEX(`relative_order`);
ALTER TABLE `dt_educations` ADD INDEX(`name`);
ALTER TABLE `dt_educations` ADD INDEX(`relative_order`);
ALTER TABLE `dt_eye_colors` ADD INDEX(`name`);
ALTER TABLE `dt_eye_colors` ADD INDEX(`relative_order`);
ALTER TABLE `dt_faq` ADD INDEX(`name`);
ALTER TABLE `dt_faq` ADD INDEX(`show_it`);
ALTER TABLE `dt_faq` ADD INDEX(`relative_order`);
ALTER TABLE `dt_faq_answers` ADD INDEX(`faq_topic_id`);
ALTER TABLE `dt_faq_answers` ADD INDEX(`name`);
ALTER TABLE `dt_favourites` ADD INDEX(`profile_id`);
ALTER TABLE `dt_favourites` ADD INDEX(`owner_id`);
ALTER TABLE `dt_food` ADD INDEX(`name`);
ALTER TABLE `dt_food` ADD INDEX(`relative_order`);
ALTER TABLE `dt_genders` ADD INDEX(`name`);
ALTER TABLE `dt_genders` ADD INDEX(`free_trial`);
ALTER TABLE `dt_genders` ADD INDEX(`relative_order`);
ALTER TABLE `dt_hair_colors` ADD INDEX(`name`);
ALTER TABLE `dt_hair_colors` ADD INDEX(`relative_order`);
ALTER TABLE `dt_heights` ADD INDEX(`name`);
ALTER TABLE `dt_heights` ADD INDEX(`relative_order`);
ALTER TABLE `dt_hot_lists` ADD INDEX(`owner_member_id`);
ALTER TABLE `dt_hot_lists` ADD INDEX(`member_id`);
ALTER TABLE `dt_hot_lists` ADD INDEX(`hotlist_type`);
ALTER TABLE `dt_hot_lists` ADD INDEX(`timestamp`);
ALTER TABLE `dt_im_messages` ADD INDEX(`rid`);
ALTER TABLE `dt_im_messages` ADD INDEX(`sid`);
ALTER TABLE `dt_im_messages` ADD INDEX(`time`);
ALTER TABLE `dt_im_messages` ADD INDEX(`is_read`);
ALTER TABLE `dt_interests` ADD INDEX(`name`);
ALTER TABLE `dt_interests` ADD INDEX(`relative_order`);
ALTER TABLE `dt_interests_x` ADD INDEX(`profile_id`);
ALTER TABLE `dt_interests_x` ADD INDEX(`interest_id`);
ALTER TABLE `dt_interests_x` ADD INDEX(`val`);
ALTER TABLE `dt_lang_rates` ADD INDEX(`name`);
ALTER TABLE `dt_lang_rates` ADD INDEX(`relative_order`);
ALTER TABLE `dt_languages` ADD INDEX(`name`);
ALTER TABLE `dt_languages` ADD INDEX(`relative_order`);
ALTER TABLE `dt_marital_status` ADD INDEX(`name`);
ALTER TABLE `dt_marital_status` ADD INDEX(`relative_order`);
ALTER TABLE `dt_matchfinder` ADD INDEX(`member_id`);
ALTER TABLE `dt_matchfinder` ADD INDEX(`age_from`);
ALTER TABLE `dt_matchfinder` ADD INDEX(`age_to`);
ALTER TABLE `dt_matchfinder` ADD INDEX(`country`);
ALTER TABLE `dt_matchfinder` ADD INDEX(`race`);
ALTER TABLE `dt_matchfinder` ADD INDEX(`marital_status`);
ALTER TABLE `dt_matchfinder` ADD INDEX(`education`);
ALTER TABLE `dt_matchfinder` ADD INDEX(`hair_color`);
ALTER TABLE `dt_matchfinder` ADD INDEX(`eye_color`);
ALTER TABLE `dt_matchfinder` ADD INDEX(`food`);
ALTER TABLE `dt_matchfinder` ADD INDEX(`occupation`);
ALTER TABLE `dt_matchfinder` ADD INDEX(`state`);
ALTER TABLE `dt_matchfinder` ADD INDEX(`city`);
ALTER TABLE `dt_matchfinder` ADD INDEX(`body_type`);
ALTER TABLE `dt_matchfinder` ADD INDEX(`religion`);
ALTER TABLE `dt_members` ADD INDEX(`login`);
ALTER TABLE `dt_members` ADD INDEX(`pswd`);
ALTER TABLE `dt_members` ADD INDEX(`email`);
ALTER TABLE `dt_members` ADD INDEX(`name`);
ALTER TABLE `dt_members` ADD INDEX(`gender`);
ALTER TABLE `dt_members` ADD INDEX(`age`);
ALTER TABLE `dt_members` ADD INDEX(`country`);
ALTER TABLE `dt_members` ADD INDEX(`looking_for`);
ALTER TABLE `dt_members` ADD INDEX(`ip_addr`);
ALTER TABLE `dt_members` ADD INDEX(`reg_date`);
ALTER TABLE `dt_members` ADD INDEX(`status`);
ALTER TABLE `dt_members` ADD INDEX(`system_status`);
ALTER TABLE `dt_members` ADD INDEX(`system_status_end`);
ALTER TABLE `dt_members` ADD INDEX(`unlimited`);
ALTER TABLE `dt_members` ADD INDEX(`unlimited_end`);
ALTER TABLE `dt_members` ADD INDEX(`matchfinder`);
ALTER TABLE `dt_members` ADD INDEX(`user_rights`);
ALTER TABLE `dt_messages` ADD INDEX(`sid`);
ALTER TABLE `dt_messages` ADD INDEX(`rid`);
ALTER TABLE `dt_messages` ADD INDEX(`subject`);
ALTER TABLE `dt_messages` ADD INDEX(`timesent`);
ALTER TABLE `dt_messages` ADD INDEX(`is_read`);
ALTER TABLE `dt_messages` ADD INDEX(`show_in_inbox`);
ALTER TABLE `dt_messages` ADD INDEX(`show_in_sent`);
ALTER TABLE `dt_messages` ADD INDEX(`is_deleted`);
ALTER TABLE `dt_messages` ADD INDEX(`is_deleted2`);
ALTER TABLE `dt_newsletter_queue` ADD INDEX(`email`);
ALTER TABLE `dt_newsletter_queue` ADD INDEX(`subject`);
ALTER TABLE `dt_newsletter_queue` ADD INDEX(`timestamp`);
ALTER TABLE `dt_occupations` ADD INDEX(`name`);
ALTER TABLE `dt_occupations` ADD INDEX(`relative_order`);
ALTER TABLE `dt_payment_security` ADD INDEX(`member_id`);
ALTER TABLE `dt_payment_security` ADD INDEX(`order_id`);
ALTER TABLE `dt_payment_security` ADD INDEX(`security_key`);
ALTER TABLE `dt_payment_security` ADD INDEX(`amount`);
ALTER TABLE `dt_payment_security` ADD INDEX(`order_date`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_1`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_10`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_11`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_12`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_12`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_12`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_13`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_14`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_15`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_16`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_17`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_18`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_19`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_2`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_20`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_3`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_4`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_5`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_6`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_7`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_8`);
ALTER TABLE `dt_photos` ADD INDEX(`filename_9`);
ALTER TABLE `dt_photos` ADD INDEX(`member_id`);
ALTER TABLE `dt_prepared_members` ADD INDEX(`login`);
ALTER TABLE `dt_prepared_members` ADD INDEX(`pswd`);
ALTER TABLE `dt_prepared_members` ADD INDEX(`email`);
ALTER TABLE `dt_prepared_members` ADD INDEX(`ip_addr`);
ALTER TABLE `dt_prepared_members` ADD INDEX(`step`);
ALTER TABLE `dt_prepared_members` ADD INDEX(`idate`);
ALTER TABLE `dt_privacy` ADD INDEX(`member_id`);
ALTER TABLE `dt_privacy` ADD INDEX(`online_yn`);
ALTER TABLE `dt_privacy` ADD INDEX(`vkiss_yn`);
ALTER TABLE `dt_privacy` ADD INDEX(`profiles_yn`);
ALTER TABLE `dt_privacy` ADD INDEX(`IM_yn`);
ALTER TABLE `dt_privacy` ADD INDEX(`featured_yn`);
ALTER TABLE `dt_privacy` ADD INDEX(`HL_messaged_yn`);
ALTER TABLE `dt_privacy` ADD INDEX(`HL_im_yn`);
ALTER TABLE `dt_privacy` ADD INDEX(`HL_viewed_yn`);
ALTER TABLE `dt_privacy` ADD INDEX(`HL_kissed_yn`);
ALTER TABLE `dt_privacy` ADD INDEX(`HL_favorite_yn`);
ALTER TABLE `dt_privacy` ADD INDEX(`bday_email`);
ALTER TABLE `dt_privacy` ADD INDEX(`sms_yn`);
ALTER TABLE `dt_profile` ADD INDEX(`age_from`);
ALTER TABLE `dt_profile` ADD INDEX(`age_to`);
ALTER TABLE `dt_profile` ADD INDEX(`body_type`);
ALTER TABLE `dt_profile` ADD INDEX(`children`);
ALTER TABLE `dt_profile` ADD INDEX(`city`);
ALTER TABLE `dt_profile` ADD INDEX(`country`);
ALTER TABLE `dt_profile` ADD INDEX(`drinking`);
ALTER TABLE `dt_profile` ADD INDEX(`education`);
ALTER TABLE `dt_profile` ADD INDEX(`email`);
ALTER TABLE `dt_profile` ADD INDEX(`eye_color`);
ALTER TABLE `dt_profile` ADD INDEX(`food`);
ALTER TABLE `dt_profile` ADD INDEX(`gender`);
ALTER TABLE `dt_profile` ADD INDEX(`hair_color`);
ALTER TABLE `dt_profile` ADD INDEX(`height`);
ALTER TABLE `dt_profile` ADD INDEX(`lang_1`);
ALTER TABLE `dt_profile` ADD INDEX(`lang_1_rate`);
ALTER TABLE `dt_profile` ADD INDEX(`lastlogin`);
ALTER TABLE `dt_profile` ADD INDEX(`latitude`);
ALTER TABLE `dt_profile` ADD INDEX(`longitude`);
ALTER TABLE `dt_profile` ADD INDEX(`looking_for`);
ALTER TABLE `dt_profile` ADD INDEX(`marital_status`);
ALTER TABLE `dt_profile` ADD INDEX(`member_id`);
ALTER TABLE `dt_profile` ADD INDEX(`name`);
ALTER TABLE `dt_profile` ADD INDEX(`occupation`);
ALTER TABLE `dt_profile` ADD INDEX(`race`);
ALTER TABLE `dt_profile` ADD INDEX(`religion`);
ALTER TABLE `dt_profile` ADD INDEX(`smoking`);
ALTER TABLE `dt_profile` ADD INDEX(`state`);
ALTER TABLE `dt_profile` ADD INDEX(`status`);
ALTER TABLE `dt_profile` ADD INDEX(`view_count`);
ALTER TABLE `dt_profile` ADD INDEX(`zipcode`);
ALTER TABLE `dt_promotion` ADD INDEX(`duration`);
ALTER TABLE `dt_promotion` ADD INDEX(`unique_code`);
ALTER TABLE `dt_promotion` ADD INDEX(`unique_member`);
ALTER TABLE `dt_promotion` ADD INDEX(`code`);
ALTER TABLE `dt_promotion_list` ADD INDEX(`promotion_id`);
ALTER TABLE `dt_promotion_list` ADD INDEX(`duration`);
ALTER TABLE `dt_promotion_list` ADD INDEX(`member_id`);
ALTER TABLE `dt_promotion_list` ADD INDEX(`time`);
ALTER TABLE `dt_queue` ADD INDEX(`member_id`);
ALTER TABLE `dt_queue` ADD INDEX(`mmih_id`);
ALTER TABLE `dt_queue` ADD INDEX(`timestamp`);
ALTER TABLE `dt_races` ADD INDEX(`name`);
ALTER TABLE `dt_races` ADD INDEX(`relative_order`);
ALTER TABLE `dt_reasons` ADD INDEX(`name`);
ALTER TABLE `dt_reasons` ADD INDEX(`relative_order`);
ALTER TABLE `dt_relationship` ADD INDEX(`name`);
ALTER TABLE `dt_relationship` ADD INDEX(`relative_order`);
ALTER TABLE `dt_relationship_x` ADD INDEX(`profile_id`);
ALTER TABLE `dt_relationship_x` ADD INDEX(`relationship_id`);
ALTER TABLE `dt_relationship_x` ADD INDEX(`val`);
ALTER TABLE `dt_religions` ADD INDEX(`name`);
ALTER TABLE `dt_religions` ADD INDEX(`relative_order`);
ALTER TABLE `dt_saved_searches` ADD INDEX(`member_id`);
ALTER TABLE `dt_saved_searches` ADD INDEX(`search_name`);
ALTER TABLE `dt_saved_searches` ADD INDEX(`timestamp`);
ALTER TABLE `dt_smoking` ADD INDEX(`name`);
ALTER TABLE `dt_smoking` ADD INDEX(`relative_order`);
ALTER TABLE `dt_sms_providers` ADD INDEX(`name`);
ALTER TABLE `dt_sms_providers` ADD INDEX(`provider_domain`);
ALTER TABLE `dt_sms_providers` ADD INDEX(`relative_order`);
ALTER TABLE `dt_sms_queue` ADD INDEX(`phone_number`);
ALTER TABLE `dt_sms_queue` ADD INDEX(`provider`);
ALTER TABLE `dt_sms_queue` ADD INDEX(`subject`);
ALTER TABLE `dt_sms_queue` ADD INDEX(`message`);
ALTER TABLE `dt_sms_queue` ADD INDEX(`timestamp`);
ALTER TABLE `dt_sms_settings` ADD INDEX(`member_id`);
ALTER TABLE `dt_sms_settings` ADD INDEX(`phone_number`);
ALTER TABLE `dt_sms_settings` ADD INDEX(`provider`);
ALTER TABLE `dt_stamps_balance` ADD INDEX(`member_id`);
ALTER TABLE `dt_stamps_balance` ADD INDEX(`balance`);
ALTER TABLE `dt_states` ADD INDEX(`parent_id`);
ALTER TABLE `dt_states` ADD INDEX(`name`);
ALTER TABLE `dt_states` ADD INDEX(`relative_order`);
ALTER TABLE `dt_tell_friend` ADD INDEX(`member_id`);
ALTER TABLE `dt_tell_friend` ADD INDEX(`email_count`);
ALTER TABLE `dt_tell_friend` ADD INDEX(`timestamp`);
ALTER TABLE `dt_usersonline` ADD INDEX(`timestamp`);
ALTER TABLE `dt_usersonline` ADD INDEX(`ip`);
ALTER TABLE `dt_usersonline` ADD INDEX(`login`);
ALTER TABLE `dt_usersonline` ADD INDEX(`userid`);
ALTER TABLE `dt_usersonline` ADD INDEX(`session_id`);
ALTER TABLE `dt_videos` ADD INDEX(`member_id`);
ALTER TABLE `dt_videos` ADD INDEX(`filename_1`);

I Have webdate 2

try running the script I posted should work for 2.0.... this script will only index database that exist it will not re-write your database.

I got "Could not connect to mysql" ...........why ???