...

View Full Version : Why is a simple SELECT taking a full minute to process?



JohnDubya
02-15-2007, 11:42 PM
I've got a SELECT query like this:


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:


<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?

Fumigator
02-16-2007, 12:15 AM
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.

felgall
02-16-2007, 01:23 AM
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.

JohnDubya
02-16-2007, 03:02 AM
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.

Fumigator
02-16-2007, 05:13 AM
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).



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

JohnDubya
02-16-2007, 04:46 PM
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?

Fumigator
02-16-2007, 05:25 PM
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]).

JohnDubya
02-16-2007, 05:49 PM
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! :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum