Slow execution?

using WAMP server on 4G laptop

[wkpride]

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

[wkpride]

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.

[jezzer300]

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!

[eeek]

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.

[coopster]

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