SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Florence, Italy
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Some time ago I posted about an extremely complicated query and I was using BIG_TABLES to get by the "tables full" error. Unfortunately that froze everything up on the site and my ISP was not happy!

    They told me that I was generating 500+ MB temp tables and that for what I want to do I need an own server (not shared with others) with at least 4 GB and definitely needed and Oracle database server.

    Up until now I've reduced the query, but I'm positive in the future it will have to get more complicated.

    Three questions:
    1) Is it difficult to switch from PHP/MySQL to PHP/Oracle - I presume it's possible?!
    2) Any other specific requirements for huge/complicated databases
    3) Anyone know any ISP that offer this????

    Thanks in advance!

    Willow

  2. #2
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP has built in Oracle database functions similar to the mySQL ones - however as Oracle SQL syntax is significantly different from mySQL (well, signifacntly different enough anyway) you'll be looking at pretty mucha completel re-write of all your database accessing code.

    I've never heard of an ISP that provides Oracle - there will certainly be some out there but expect to pay a LOT of money for them...

    Just out of interest what application are you running that's so complicated it knocks up 500MB+ temp tables?

    Cheers,

    Skunk

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Florence, Italy
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Uhm. I'm not sure what you mean with "what application" I'm running, but the reason why the query is generating such huge tables is because it concerns a supplier search page where visitors can use keywords and checkboxes for various product functions and industries in which these suppliers have experience. It's just that there are so many to many relationships and I have many intermediate tables.

    I honestly didn't have a clue that this was going to be a problem. This is the my first project EVER done with PHP/Mysql
    (I'm actually a newbie).

    And... if ISPs don't provide Oracle, how does it work? People with their own server?
    Or what other database servers that ISPs DO provide should I be looking at for extensive databases?

    Willow

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have heard of hosts that provide Oracle - and as Skunk says it will be very expensive. Also, just changing to a platform that provides more mega-grunt is a stealth solution.

    You may be better of finding an experienced data base designer that can redesign your database tables, indexes and queries to optimise performance. It will be cheeper in the long run. When performance becomes an issue as in your case - you need to start deviating from relational data modelling theory and start designing for performance instead. This may involve "denormalising" your database design. I am also a novice programmer/dba and I would be looking for professional help in this situation. Good luck

    Out of curiosity - what's the URL?

  5. #5
    Serial Publisher silver trophy aspen's Avatar
    Join Date
    Aug 1999
    Location
    East Lansing, MI USA
    Posts
    12,937
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Read this thread:

    http://www.sitepointforums.com/showt...ghlight=oracle

    Pay attention to the posts by Wayne.

    Get out your checkbook.
    Chris Beasley - I publish content and ecommerce sites.
    Featured Article: Free Comprehensive SEO Guide
    My Guide to Building a Successful Website
    My Blog|My Webmaster Forums

  6. #6
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Florence, Italy
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freakysid


    Out of curiosity - what's the URL?
    Well, we haven't gone live yet, one of the reasons me not being sure that it's okay
    I will let you know when it's really up & running. Perhaps your right and do some re-designing - or have it done!

    Willow

  7. #7
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Florence, Italy
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by aspen
    Read this thread:

    http://www.sitepointforums.com/showt...ghlight=oracle

    Pay attention to the posts by Wayne.

    Get out your checkbook.
    Thanks for pointing this out. I did do a search before posting and had seen this thread but obviousely wasn't paying any much attention because I missed Wayne's major post!

    BTW, does the "table full" problem only occur when to many tables are being consulted? I ask this because this happened when my query was complicated but I only had like 3 suppliers in my database. I've taken out the extra search options, but will I get a "tables full" error if I have like one thousand suppliers but "searched" for with a simple query?

    Willow

  8. #8
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tell ya what -- a 500MB temp table is quite large but not really "insanely" large as to require you to spend upwards of $75,000 on Oracle.

    Post the exact query that is causing the problem.
    For each table involved in this large join, provide:
    1) output of "SELECT COUNT( * ) FROM table_in_join;"
    2) the schema of each table (e.g. CREATE TABLE bob ( ... )
    3) the indexes on each table (there's a way to do this in MySQL if you don't have your install scripts up to date.. maybe SHOW INDEXES table_name..?)

    What hardware is the database server on? What OS?

  9. #9
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    P.S.

    My dissertation on MySQL v. Sybase v. Oracle is here:
    http://sitepointforums.com/showthrea...5&pagenumber=2

  10. #10
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    With all due respect to Wayne Luke, and while his posts are interesting they are not very relevent.

    I can just as validly talk about the fact that I have Oracle i8.1.6 installed on a Pentium 166MMX with 192MB RAM on a 5GB partition on an IDE Hard Disk running RedHat 7 distro of Linux. Software cost of aquisition AUS $20.00 (about US $10.50, bought some non-official redhat distro cds) . Ongoing licence fees $0.00. Maintenance costs $0.00.

    If you need to find a web host that offers Oracle - they are out there. Just do a search and you will find hosts such as:

    http://hosting.valudeal.com/oraclebasic/
    from $300 per month

    Or you can host your database on Oracle's servers. I read somewhere that it costs about $100 per gig per month. Oracle's web page about hosting is not very information friendly though:http://www.oracle.com/ip/solve/ihost/

    Anway, I see MattR has entered the thread - he's your man!
    Last edited by freakysid; Feb 28, 2001 at 09:34.

  11. #11
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sid,

    I agree with you on those points (and thanks for the mention! ) -- but I think we can help him more if we try and find the root cause of the problem (could he be doing table scans? is every field a text column? etc.) before we go ahead and say "Pay for Oracle" -- especially if it's not needed.

    Coming from one who ported an application to another environment Sun Solaris / Sybase from Linux / MySQL it's fairly painful and shouldn't be undertaken lightly -- especially if all other options haven't been exhausted first.
    Last edited by MattR; Feb 28, 2001 at 09:38.

  12. #12
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    He, he - you can help him Matt - this is way over my head. Although if its OK with you I'd like to watch

    Indead, what you suggest is also the gist of my first post. My second post was more in response to the thread that aspen posted and the misconception that (while indeed more expensive than mySQL) running Oracle on a web server is not necessarily going to cost hundreds of thousands or millions of dollars.

  13. #13
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Florence, Italy
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll get back to you with the exact details of the query ASAP - I'm sure I've done things that aren't right or definitely can be done better.

    Unfortunately right now my 9 month old has a 104 F fever so finding time is difficult!


    Willow - SHE

  14. #14
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ooops - bad assumption!

  15. #15
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Willow
    Unfortunately right now my 9 month old has a 104 F fever so finding time is difficult!
    Yikes! She is definitly more important than some silly SQL query. We'll be here whenever you're ready (and she's all better! ).

    I hope she gets well soon!

  16. #16
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Florence, Italy
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by MattR
    Tell ya what -- a 500MB temp table is quite large but not really "insanely" large as to require you to spend upwards of $75,000 on Oracle.

    Post the exact query that is causing the problem.
    For each table involved in this large join, provide:
    1) output of "SELECT COUNT( * ) FROM table_in_join;"
    2) the schema of each table (e.g. CREATE TABLE bob ( ... )
    3) the indexes on each table (there's a way to do this in MySQL if you don't have your install scripts up to date.. maybe SHOW INDEXES table_name..?)

    What hardware is the database server on? What OS?
    Hmmm.... in trying to answer your questions I notice how much of a newbie I am!
    As you can see (I hope!), the query below has the basic select, from, where variables and depending on what
    keywords and/or checkboxes are checked $where .= and $from .= is added (got this from Kevin Yank's tutorial).
    When I just did everything like the "industry type variable" (checkboxes) it worked fine. But then I
    discovered that I needed an (xxxx OR xxxx OR xxxx OR xxxx) situation regarding the "Customer Analysis,
    Function, Marketing and Service" checkboxes - which I have delimited by "======" so you can easily see
    what I'm talking about. There's probably a much simpler way to do it but the only way I could get the OR thing
    to work without an AND situation and without the query not working because the something was missing
    in the parenthesis and, finally, being sure that if none of the checkboxes were checked that it
    would just be ignored was to set up a sort of "bogus" else situation. I actually wonder if perhaps I'm
    the only one who understands what I've done. In any event, it worked a couple of times (with just 3 or 4 rows
    in the supplier table but then I started to get the "table full" error. And we're thinking that perhaps
    in the future we might have near 1000 suppliers (perhaps exaggerated but just let us assume so).
    To avoid the table full error, for now I've removed the bogus else statement


    1) The output of select (*) count of the first 5 tables is fixed. The rest increase as more
    suppliers are entered into the database. The amount now is just a test.

    productcharsanalysis 6
    productcharsfunctions 9
    productcharsmarketing 6
    productcharsservice 7
    industries 25
    SupplierIndustries 10
    SupplierProductCharAnalysis 8
    SupplierProductCharFunctions 10
    SupplierProductCharMarketing 10
    SupplierProductCharService 11
    suppliers 4

    2) I'm not sure what you mean (even with your e.g. CREATE TABLE) with the table schema.

    3) I have no Indexes in my tables! Only the primary keys which in Supplier are Supplier ID
    in e.g. industries it's IndustryID and supplierindustries none since supplierindustries just holds
    the SupplierID and IndustryID (as the "lookup" table). The same for the analysis, functions, marketing
    and service tables.

    I have all the primary keys set as int(11) and all the other fields as varchar(50).

    4) Below I've directly copied the info from my ISP's site (Italian BTW)
    - Pentium III 550/600/650/733 MHZ - RAM 256/512/768 GB
    - Sistema Operativo: UNIX FreeBSD System - Linux 2.2.16 (Red Hat 6.1)
    - Apache 1.3.9 - 1.3.12 (Webserver)



    I hope you can weed through what I've done and in any event really appreciate you spending time
    on this!



    QUERY

    // basic select variables

    $select = "SELECT DISTINCT suppliers.SupplierID, SupplierName, SupplierAddress, SupplierCity,
    SupplierState, SupplierProvince, SupplierPostalCode, SupplierCountry, SupplierPhone, SupplierFax,
    SupplierURL, Reg, SupplierType";
    $from = " FROM suppliers";
    $where = " WHERE suppliers.Confirmed ='yes'";
    $order = " ORDER BY Reg desc";

    // type variable

    if ($type !=""){
    $where .= " AND suppliers.SupplierType='$type'";
    }

    // industry type variable
    $num = count($industryids);
    if ($num > 0) {
    while(list($key,$val)=each($industryids)){
    $array .=sprintf("'%s',",$val);
    }
    $array = substr($array,0,-1);

    if ($array !=""){

    $from .= ", SupplierIndustries";
    $where .= " AND SupplierIndustries.SupplierID=suppliers.SupplierID AND SupplierIndustries.IndustryID IN ($array)";
    }
    }

    // country type variable

    if ($country != "") {
    $where .= " AND SupplierCountry='$country'";
    }


    // city search variable

    if ($city != "") {
    $where .= " AND SupplierCity LIKE '%$city%'";
    }

    // company search variable

    if ($company != "") {
    $where .= "AND SupplierName LIKE '%$company%'";
    }

    // keyword search variable

    if ($keyword != "") {
    $where .= " AND (SupplierName LIKE '%$keyword%' OR SupplierAddress LIKE '%$keyword%' OR
    SupplierCity LIKE '%$keyword%' OR SupplierState LIKE '%$keyword%' OR SupplierProvince
    LIKE '%$keyword%' OR SupplierCountry LIKE '%$keyword%' OR SupplierPhone LIKE '%$keyword%' OR
    SupplierFax LIKE '%$keyword%' OR SupplierType LIKE '%$keyword%' OR SupplierURL LIKE '%$keyword%' OR ContactLname LIKE '%$keyword%' OR CompanyDescription LIKE '%$keyword%' OR
    AssociateProducts LIKE '%$keyword%')";


    }

    ============================================================

    // Customer Analysis search variable
    $num = count($analysisids);
    if ($num >0) {
    while(list($key,$val)=each($analysisids)){
    $arraya .=sprintf("'%s',",$val);
    }
    $arraya = substr($arraya,0,-1);

    if ($arraya !=""){
    $froma = ", SupplierProductCharAnalysis";
    $wherea = " SupplierProductCharAnalysis.SupplierID=suppliers.SupplierID AND SupplierProductCharAnalysis.ProductCharID IN ($arraya)";
    }
    }
    else {
    $froma = ", SupplierProductCharAnalysis";
    $wherea = " SupplierProductCharAnalysis.SupplierID=suppliers.SupplierID AND SupplierProductCharAnalysis.ProductCharID IN ('0')";
    $noarraya = "b";

    }

    // function search variable
    $num = count($functionsids);
    if ($num > 0) {

    while(list($key,$val)=each($functionsids)){
    $arrayf .=sprintf("'%s',",$val);
    }
    $arrayf = substr($arrayf,0,-1);

    if ($arrayf !=""){
    $fromf = ", SupplierProductCharFunctions";
    $wheref = " OR SupplierProductCharFunctions.SupplierID=suppliers.SupplierID AND SupplierProductCharFunctions.ProductCharID IN ($arrayf)";
    }
    }
    else {
    $fromf = ", SupplierProductCharFunctions";
    $wheref = " OR SupplierProductCharFunctions.SupplierID=suppliers.SupplierID AND SupplierProductCharFunctions.ProductCharID IN ('0')";
    $noarrayf = "b";
    }

    // marketing search variable
    $num = count($marketingids);
    if ($num > 0) {

    while(list($key,$val)=each($marketingids)){
    $arraym .=sprintf("'%s',",$val);
    }
    $arraym = substr($arraym,0,-1);

    if ($arraym !=""){
    $fromm = ", SupplierProductCharMarketing";
    $wherem = " OR SupplierProductCharMarketing.SupplierID=suppliers.SupplierID AND SupplierProductCharMarketing.ProductCharID IN ($arraym)";
    }
    }
    else {
    $fromm = ", SupplierProductCharMarketing";
    $wherem = " OR SupplierProductCharMarketing.SupplierID=suppliers.SupplierID AND SupplierProductCharMarketing.ProductCharID IN ('0')";
    $noarraym = "b";

    }
    // service search variable
    $num = count($serviceids);
    if ($num > 0) {
    while(list($key,$val)=each($serviceids)){
    $arrays .=sprintf("'%s',",$val);
    }
    $arrays = substr($arrays,0,-1);

    if ($arrays !=""){
    $froms = ", SupplierProductCharService";
    $wheres = " OR SupplierProductCharService.SupplierID=suppliers.SupplierID AND SupplierProductCharService.ProductCharID IN ($arrays)";
    }
    }
    else {
    $froms = ", SupplierProductCharService";
    $wheres = " OR SupplierProductCharService.SupplierID=suppliers.SupplierID AND SupplierProductCharService.ProductCharID IN ('0')";
    $noarrays = "b";
    }


    if ($noarraya AND $noarrayf AND $noarraym AND $noarrays)
    {

    $where .= "";
    }
    else {
    $where .= " AND ($wherea $wheref $wherem $wheres)";
    $from .= " $froma $fromf $fromm $froms";
    }

    =======================================================
    // Query



    $result=mysql_query($select . $from . $where . $order);

  17. #17
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Florence, Italy
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by MattR


    Yikes! She is definitly more important than some silly SQL query. We'll be here whenever you're ready (and she's all better! ).

    I hope she gets well soon!
    Thanks, I just saw this message! She's sleeping now, but if the fever stays up we'll probably have to go to the hospital So even though I've just posted my silly SQL query, I might not be able to get back to it for a few days. Thanks

    Willow

  18. #18
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Florence, Italy
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Willow


    Thanks, I just saw this message! She's sleeping now, but if the fever stays up we'll probably have to go to the hospital So even though I've just posted my silly SQL query, I might not be able to get back to it for a few days. Thanks

    Willow
    I know! Bad form to reply to my own post. But just wanted to say that I'm "back". No more fevers

  19. #19
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good to hear that! Hope she's doing better!

    I've been working on the query (the PHP code is kind of hard to look at ) but I'd say you DEFINITELY need indexes on the columns in the 'where' clause. That will allow you to NOT do table scans on each table. The only problem is that you have variable columns in the where clause (sometimes they show up and sometimes they don't) which means it will be difficult to come up with optimal indexes.

    I'd suggest determining what the most common method of searching is for and create a multi-column index on that, say 'CREATE INDEX col1_col2_etc ON table( col1, col2, etc. );'.

    Then for the others add an index on each one to create a composite index for the optimizer to choose when querying.

    Another thing -- with most tables being as small as they are I'm not sure how you can generate 500+ MB work tables unless you're joining the tables together without properly using a WHERE clause.

    Tomorrow I'll dissect it as much as possible to see what is going wrong, but I'd say in the mean time add indexes like crazy where needed and then we can see what is happening with the joins (if you were to join every table together incorrectly (Cartesian product) you'd get a massive work table with 19958400000 rows!!!!)

  20. #20
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Florence, Italy
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1) Sorry for the "unreadable" code but that's the result of self-eduction

    2)I have to admit I don't have a clue about indexes and what you're suggesting - never came across it in the tutorials! - so I'm going to have to study how that exactly works.

    3) I have NO DOUBT I've done some weird things and YES... my ISP told me I had created Cartesian product - although I wasn't sure what it meant!

    Thank you so much for all the time you're giving this to help me out. You don't know HOW MUCH I appreciate it.

    Willow

  21. #21
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There's an excellent beginners tutorial on indexing here:

    http://www.phpbuilder.com/columns/tim20010110.php3

  22. #22
    SitePoint Enthusiast
    Join Date
    Jan 2001
    Location
    Florence, Italy
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the link! I now know how to do it but I'm still not sure how in my case it would be implemented. If I understand correctly, wouldn't a primary key be like an index?

    I'll try to give a short and clear overview of my tables - also for you Matt

    Basically each supplier can belong to 5 categories. One being the industry they have experience in and 4 categories that say something about the functionality of their products/services (service functionality, marketing functionality, functions functionality and analysis functionality).

    The suppliers table has as primary key SupplierID and then the usual fields being name, address, phone, etc.

    The industries table has an IndustryID (Primary Key) and IndustryDescription

    e.g.
    1 Automotive
    2 Bank
    3 Manufacturing etc.

    Then I have the "lookup" table (according to Kevin Yank's tutorial) in this case SuppliersIndustries which has
    SupplierID
    IndustryID
    without however any primary keys because the same SupplierID and IndustryID can be in there several times since a supplier can have experience in several industries.

    I have done this the same way for the other categories.

    Then, on the search page, the visitor can specify in what industries the supplier must have experience and what functionalities their products must have. Depending on the checkboxes checked the relevant where clause is included.

    But then I found out that if a visitor checked an item in say the marketing category and also the service category an AND situation was created in my WHERE clause. But I want it to be so that it's an OR situation i.e. if a supplier has "x" marketing functionality but NOT "y" service functionality he still comes up on the result page - and that's where my own invented weird query comes from

    Basically the most used search criteria will be the industry and the 4 product function tables (and thus relevant lookup tables). Perhaps also the "country" search criteria but the other keywords will probably not be used much.

    In any event, it's still not so clear to me how I would implement the indexes in those 5 search criteria. But I do hope that what I'm doing is a bit more clear now!

    Willow


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
  •