Welcome to WebmasterWorld Guest from 23.22.182.29

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Slow execution?

using WAMP server on 4G laptop

     
1:26 am on Mar 8, 2009 (gmt 0)

Junior Member

10+ Year Member

joined:Feb 13, 2005
posts:63
votes: 0


simple search (SELECT * FROM table WHERE record LIKE 12345;)

And it's now taking almost 24 seconds to find the record. The db has 1.6m records, but before I updated some records, it seemed to work faster...
So, that is REALLY slow, isn't it?

Here is some of the code:
if (isset($_POST['submit']))
{$polenum=mysql_real_escape_string(trim($_POST['polenum'])); mysql_select_db("poledb", $dbh);
$result = mysql_query("SELECT * FROM kenpoles WHERE polenum LIKE '%$polenum%'");
echo "<tr>";
echo "<table border='1' cellspacing='1' cellpadding='1' bordercolor='#7F25FA'>
<col width='100px'/>
<col width='100px'/>
<col width='100px'/>
<tr>
<th>Edit ?</th>
<th>Pole #</th>
<th>Service Center</th>
<th>Sub #</th>
<th>Sub Name</th>
<th>Circuit #</th>
<th>Circuit Name</th>

</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" ?> <input type="submit" name="submit" value="Edit" /> <?php "</td>";
echo "<td>" . $row['polenum'] . "</td>";
echo "<td>" . $row['scname'] . "</td>";
echo "<td>" . $row['subnum'] . "</td>";
echo "<td>" . $row['subname'] . "</td>";
echo "<td>" . $row['cktnum'] . "</td>";
echo "<td>" . $row['cktname'] . "</td>";

Thoughts?

Thanks,

KP

4:11 am on Mar 8, 2009 (gmt 0)

Junior Member

10+ Year Member

joined:Feb 13, 2005
posts:63
votes: 0


I eliminated a few columns (unused) and the time dropped to 2.4sec

Not sure if was from dropping columns or because the db acts funky.. when I run new programs.

6:05 pm on Mar 9, 2009 (gmt 0)

Junior Member

10+ Year Member

joined:Apr 26, 2004
posts: 137
votes: 0


I would consider if you need to use %LIKE% all the time which I guess needs to run down the whole index. Also, aren't there any other rows you can use in the WHERE clause (not using LIKE) to reduce the amount of work?

If this is a really simple table, which is mainly searched on a lot of the time you may consider using a myISAM table instead of InnoDB - which I assume you are using?

I hope polenum is in an index!

10:12 pm on Mar 16, 2009 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 20, 2004
posts:875
votes: 2


I would consider if you need to use %LIKE% all the time which I guess needs to run down the whole index

At 24 seconds it probably isn't even using an index. If this were Postgresql, I'd say to use EXPLAIN to find out what kind of plan the query is using.

10:41 pm on Mar 16, 2009 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


You can use EXPLAIN [dev.mysql.com] with MySQL too.