homepage Welcome to WebmasterWorld Guest from 54.226.0.225
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Slow execution?
using WAMP server on 4G laptop
wkpride




msg:3865465
 1:26 am on Mar 8, 2009 (gmt 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

 

wkpride




msg:3865537
 4:11 am on Mar 8, 2009 (gmt 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.

jezzer300




msg:3866477
 6:05 pm on Mar 9, 2009 (gmt 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!

eeek




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

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




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved