SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Nov 2004
    Location
    sydney
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    is database too big?

    hi, ive nearly finished making my searchengine, but I have one more question. My searchengine has the option to search neighbouring suburbs for shops if the textbox is ticked. I thought the best way to do this was to give each suburb a unique id number, and then use +1 +2 +3 -1 -2 -3 to find its neighbouring suburbs, as i have given them numbers in the way that the numbers closest to them are their neighbours, this took along time as there are 430 suburbs in sydney. it works basically like this if the checkbox is ticked it will look up the suburb database, find the suburb, get its id number, get the id numbers of its neighbours, and tell the browser to display the listings for these suburbs. the only possible problem is that their are 430 suburbs in the database. Is this too many for the script to be able to handle?

  2. #2
    SitePoint Wizard mark_W's Avatar
    Join Date
    Mar 2004
    Location
    West Midlands, United Kingdom
    Posts
    2,631
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You might have a problem with the default timeout setting, If the script takes that long then it will just die. Im not really sure about performance issues in general.

    Hth

    Mark.

  3. #3
    He's No Good To Me Dead silver trophybronze trophy stymiee's Avatar
    Join Date
    Feb 2003
    Location
    Slave I
    Posts
    23,426
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    If you've designed your database properly and wrote good code it shouldn't be a problem...unless you're on a weak or overburdoned server.

  4. #4
    SitePoint Member
    Join Date
    Nov 2004
    Location
    sydney
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes that might be a problem. since most of the hits will come at a certain time i am worried that 400 suburbs might make the server overload. But I am a newbie so im not really sure if 400 is even a big deal.

  5. #5
    He's No Good To Me Dead silver trophybronze trophy stymiee's Avatar
    Join Date
    Feb 2003
    Location
    Slave I
    Posts
    23,426
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    I suspect not. If your query is well written, your database properly designed with appropriate indices, and your code is well written you should be fine.

  6. #6
    SitePoint Member
    Join Date
    Nov 2004
    Location
    sydney
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    um....... i probably should know this but.. how do i design a database properly?
    my database is as follows
    main: list of all the names of the shops, type, suburb and id number
    suburbs: list of all 400 suburbs, with id numbers

    basically my script takes the input from the user, selects all the entries which match the type and suburb they have specified from the main database, then if they have checked the box (search neighbouring suburbs) it looks up the suburbs database, picks out the neighbouring suburbs, then tells the main database to display the entries which match the type, and new suburbs. Is that how a database should work?

    also i havent yet written the code for this, my code at the moment can just match up suburbs and types of shops from the main database.

  7. #7
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem is how to store neighbouring suburbs. Generally you have 3 options:

    1) add 'neighbours' varchar field to 'suburbs' table and store comma separated list of neighbouring ids ie '1,100,200' etc. This is effective, but you're limited to 255 chars for this field.

    2) use adjacency list ie. a table with two integer columns, if x-y pair is in the table, x and y are neighbours. This is more flexible, but you'll need an extra query.

    3) store geographical coordinates of each suburb and caclulate distance between them. This allows you to implement cool 'vicinity search' feature, but the formula is rather complex, escpecially when you need precise results.

  8. #8
    SitePoint Member
    Join Date
    Nov 2004
    Location
    sydney
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hehe i just realised an even bigger problem. My suburbs table is nothing compared to the main one, which has 1000 shops with their name and a approx 75 word description, i really need to know, roughly how long in sec it would take for a script to go through 1000 shops. heres the script below- tell me if i can get it to work better

    <?
    $username="kimbedso";
    $password="password";
    $database="kimbedso_mydb";
    $cusine=$_POST['cusine'];
    $suburb=$_POST['suburb'];

    mysql_connect(localhost,$username,$password);
    @mysql_select_db($database) or die( "Unable to select database");
    $query="SELECT * FROM better WHERE cuisine='$cusine' and suburb='$suburb' ";
    $result=mysql_query($query);

    $num=mysql_numrows($result);

    mysql_close();

    echo "<b><center>Database Output</center></b><br><br>";

    $i=0;
    while ($i < $num) {

    $name=mysql_result($result,$i,"name");
    $sub=mysql_result($result,$i,"suburb");
    $cu=mysql_result($result,$i,"cuisine");
    $des=mysql_result($result,$i,"description");


    ?>

    <table width="564" border="0" cellpadding="0" cellspacing="0">
    <tr>
    <td width="392" height="25" valign="top" bgcolor="#99CCFF"><font face="Verdana, Arial, Helvetica, sans-serif"><b><? echo $name ?></b></font></td>
    <td width="172" valign="top" bgcolor="#99CCFF">
    <div align="right"><font face="Verdana, Arial, Helvetica, sans-serif"><? echo $sub ?></font></div>
    </td>
    </tr>
    <tr bgcolor="#CCFFFF">
    <td colspan="2" height="173" valign="top"><font face="Verdana, Arial, Helvetica, sans-serif"><? echo $des ?></font></td>
    </tr>
    </table>

    <?
    $i++;
    }

    ?>


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •