Web Analytics Made Easy -
StatCounter Joining tables made things 10x slower - CodingForum

Announcement

Collapse
No announcement yet.

Joining tables made things 10x slower

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Joining tables made things 10x slower

    Hello all, I admittedly am very new to using Mysql, and databases in general.


    Originally, I had 2 tables: Table A and B. A had 20k Rows, and B as 30k. I then replaced Table A with C, which has only 1k rows, and added a third table (D), which has only 74 rows. When I do my query, it now takes about 10 times as long.

    Here are the variables I'm wondering may be causing the problem?

    1. Here is my original code:


    Code:
    $stmt = $db->prepare("
    	SELECT ElectionPlanFund.plancode, ElectionPlanFund.FundClass, ElectionPlanFund.FundTicker, United_States_Mutual_Funds.A, United_States_Mutual_Funds.B
    	FROM ElectionPlanFund
    		INNER JOIN United_States_Mutual_Funds
    	    ON ElectionPlanFund.FundTicker=United_States_Mutual_Funds.A
    	WHERE PlanCode= :userinputcode
    	ORDER by SectionNumber, SectionIndex
    	");
    Here is the new code:

    Code:
    $stmt = $db->prepare("
    	SELECT United_States_Mutual_Funds.A, United_States_Mutual_Funds.B, United_States_Mutual_Funds.M, CatRank.Cat, CatRank.CatRank, pallocations.pallocationsticker, pallocations.pallocationsplancode, pallocations.pallocationsclass, pallocations.pallocationstype, pallocations.AAA, pallocations.MAB
    	FROM pallocations
    	    INNER JOIN United_States_Mutual_Funds
    	       ON pallocations.pallocationsticker=United_States_Mutual_Funds.A
    	    INNER JOIN CatRank
    	       ON United_States_Mutual_Funds.M=CatRank.Cat
    	WHERE pallocationsplancode= :userinputcode
    	ORDER by CatRank
    	");
    Is it because I'm calling more information (more columns)? Or is it because I'm INNER JOINING 3 tables, and badly?

    2. Is it because the first 2 original tables were imported as Opendocument Spreadsheets, and now I have one as an OpenDocument Spreadsheet, and the other 2 were uploaded as CSV files?

    Would it be much faster if instead of INNER JOINING the third table (only 74 rows), I just had another column in the big table with the values from the 74 row third table?

  • #2
    What are the column types you are joining on? Are those columns indexed?
    To save time, lets just assume I am almost never wrong.

    The XY Problem
    The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

    Make A Donation https://www.paypal.me/KevinRubio

    Comment


    • #3
      Type Collation
      InnoDB utf8_general_ci

      Not indexed (or at least I didn't index them)

      Everything is uploaded to my host in a cloud setting.

      Comment


      • #4
        What is the column type? Integer,varchar?
        To save time, lets just assume I am almost never wrong.

        The XY Problem
        The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

        Make A Donation https://www.paypal.me/KevinRubio

        Comment


        • #5
          Originally posted by HalPlz View Post
          Not indexed (or at least I didn't index them)
          That's most likely your problem then. Query conditions on non-indexed fields are going to be slow. MORE so when you use JOIN. If you are performing ON or WHERE on a field, indexing it will speed it up a LOT. Night and Day performance-wise.
          Walk the dark path, sleep with angels, call the past for help.
          https://cutcodedown.com
          https://medium.com/@deathshadow

          Comment


          • #6
            When you get things running slow like this is a good indication that you don't have the right indexes set up. Indexes should be the first thing you look at when things appear to be running slow. Usually adding an extra index will improve performance dramatically. There are tools available in SQL to allow you to see how the processing is being performed that can help you to work out the most effective indexes to use.
            Stephen
            Learn Modern JavaScript - http://javascriptexample.net/
            Helping others to solve their computer problem at http://www.felgall.com/

            Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

            Comment


            • #7
              Ok, thanks everyone for pointing me to indexes. I've read up on why I would want to use them, but I can't find anything on how to actually implement indexes.


              There is also an SQL tab. I entered this:

              CREATE INDEX pallocationsindex
              ON pallocations (pallocationsplancode);

              Where pallocationsindex is the name of the index I'm creating. pallocations is my table, and pallocationsplancode is the column name I'm trying to index.

              What then? How do I know if the index is created? Where is it created? I don't see any additional columns in my table. Do I now refer to the index name in the PHP instead of the column it's based on?

              Edit: I tried re-entering that code, and it says the key already exists, so it must have been created. I just don't know where it is, and how to use it.
              Last edited by HalPlz; Sep 16, 2016, 08:02 PM.

              Comment


              • #8
                Once the index is created the database will use it when using it will make the access easier. Try running the query again and if it runs significantly faster then it is using the index. If it still runs the same then that index didn't help/
                Stephen
                Learn Modern JavaScript - http://javascriptexample.net/
                Helping others to solve their computer problem at http://www.felgall.com/

                Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

                Comment

                Working...
                X