Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts

    Why is a simple SELECT taking a full minute to process?

    I've got a SELECT query like this:

    PHP Code:
    mysql_query("SELECT cp.PersonID, s.PersonID 
                    FROM ChapterPerson AS cp, Student AS s 
                    WHERE cp.PersonID = '46539' 
                    OR s.PersonID = '46539'"
    ); 
    It takes a full minute to process! I did an EXPLAIN on it, and here's what I got:

    Code:
    <table id="table_results" border="0" cellpadding="2" cellspacing="1">
    <!-- Results table headers -->
    <tr>
            
    <th    >
        id
        </th>
                    
    <th    >
        select_type
        </th>
                    
    <th    >
        table
        </th>
                    
    <th    >
        type
        </th>
                    
    <th    >
        possible_keys
        </th>
                    
    <th    >
        key
        </th>
                    
    <th    >
        key_len
        </th>
                    
    <th    >
        ref
        </th>
                    
    <th    >
        rows
        </th>
                    
    <th    >
        Extra
        </th>
                    
    </tr>
            
    <!-- Results table body -->
        
    <tr onmouseover="setPointer(this, 0, 'over', '#D5D5D5', '#CCFFCC', '#FFCC99');" onmouseout="setPointer(this, 0, 'out', '#D5D5D5', '#CCFFCC', '#FFCC99');" onmousedown="setPointer(this, 0, 'click', '#D5D5D5', '#CCFFCC', '#FFCC99');">
                
        <td align="right" valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete0');"  bgcolor="#D5D5D5" class="nowrap">1</td>
        <td valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete0');"  bgcolor="#D5D5D5">SIMPLE</td>
        <td valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete0');"  bgcolor="#D5D5D5">cp</td>
        <td valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete0');"  bgcolor="#D5D5D5">index</td>
        <td valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete0');"  bgcolor="#D5D5D5">PersonID</td>
        <td valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete0');"  bgcolor="#D5D5D5">PersonID</td>
        <td align="right" valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete0');"  bgcolor="#D5D5D5" class="nowrap">5</td>
        <td valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete0');"  bgcolor="#D5D5D5"><i>NULL</i></td>
        <td align="right" valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete0');"  bgcolor="#D5D5D5" class="nowrap">7474</td>
        <td valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete0');"  bgcolor="#D5D5D5">Using index</td>
    
    </tr>
                
    <tr onmouseover="setPointer(this, 1, 'over', '#E5E5E5', '#CCFFCC', '#FFCC99');" onmouseout="setPointer(this, 1, 'out', '#E5E5E5', '#CCFFCC', '#FFCC99');" onmousedown="setPointer(this, 1, 'click', '#E5E5E5', '#CCFFCC', '#FFCC99');">
                
        <td align="right" valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete1');"  bgcolor="#E5E5E5" class="nowrap">1</td>
        <td valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete1');"  bgcolor="#E5E5E5">SIMPLE</td>
        <td valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete1');"  bgcolor="#E5E5E5">s</td>
        <td valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete1');"  bgcolor="#E5E5E5">index</td>
        <td valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete1');"  bgcolor="#E5E5E5">PersonID</td>
        <td valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete1');"  bgcolor="#E5E5E5">PersonID</td>
        <td align="right" valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete1');"  bgcolor="#E5E5E5" class="nowrap">4</td>
        <td valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete1');"  bgcolor="#E5E5E5"><i>NULL</i></td>
        <td align="right" valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete1');"  bgcolor="#E5E5E5" class="nowrap">16556</td>
        <td valign="top"  onmousedown="setCheckboxColumn('id_rows_to_delete1');"  bgcolor="#E5E5E5">Using where; Using index</td>
    
    </tr>
                
    </table>
    (is there any way to use HTML?)

    So it's looking through 7,400 records in the ChapterPerson table and 16,500 in the Student table. I've got an index on both the PersonID fields.

    I have plenty of other SELECT's that are similar to this one, but none of them take this long to process. Why might this SELECT be taking so long?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Silly question, but why are you selecting data you already have?

    I'd guess your query is taking a long time because you are selecting values from two different tables, joining the two tables together, but you're not putting a WHERE clause on any fields between the two tables. An index does you no good here because you're joining everything in both tables.

  • #3
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,618
    Thanks
    0
    Thanked 645 Times in 635 Posts
    You are joining one record in one table with all the records in the second and one record in the second table to all the records in the first. The total records retrieved by the query will be the combined total of the records in both tables - 23900 results - which is of course why it takes a while to process.
    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.

  • #4
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    So how could I fix this? Would it help to SELECT * in this query?

    The query is built to find out if a certain PersonID is in either the ChapterPerson or the Student tables. If the PersonID is not found, the script deletes the PersonID from the database completely.

  • #5
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Just select a count then. If your version of MySQL is >= 4.1 you can use a subquery with the "exists" keyword; otherwise probably two queries is a fine solution (there may be a way to use one query but why fight it).

    Code:
    SELECT COUNT(*)
    FROM ChapterPerson as cp
    WHERE cp.PersonID = '999'
    OR EXISTS (SELECT * FROM Student as s WHERE s.PersonID = '999')

  • #6
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    I tried that code in an SQL query in phpMyAdmin, and it gives me a syntax error. I have MySQL 4.1.21 running. Here's the exact error:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

    When I get a query like this to work (using COUNT), how do I get PHP to see if the query returned 0? Should I do a mysql_num_rows or pull out the COUNT from the query?

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I can't see the syntax error visually (plus you didn't provide the actual query you are getting the error on).

    Here's what the MySQL manual has to say on subqueries:

    http://dev.mysql.com/doc/refman/4.1/en/subqueries.html

    When you run a select count(*) query, mysql_num_rows() is going to always return 1. The actual value returned is the count. so mysql_fetch_array() will return the count in the first element of the array (i.e. $row[0]).

  • #8
    Super Moderator JohnDubya's Avatar
    Join Date
    Nov 2006
    Location
    Missouri
    Posts
    634
    Thanks
    12
    Thanked 18 Times in 18 Posts
    I used the exact query you gave me because it had the correct column names and everything, but no matter. I used COUNT(*) to check if there were any rows in those two tables, and got the amount of rows by using mysql_fetch_array, like you said. It works like a charm!

    Thanks once again, Fumi! You're awesome!


  •  

    Posting Permissions

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