Welcome to WebmasterWorld Guest from 54.157.222.62

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)

10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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)

WebmasterWorld Senior Member 10+ Year Member



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)

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



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