homepage Welcome to WebmasterWorld Guest from 54.145.172.149
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
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

5+ Year Member



 
Msg#: 3865463 posted 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

5+ Year Member



 
Msg#: 3865463 posted 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

10+ Year Member



 
Msg#: 3865463 posted 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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3865463 posted 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

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



 
Msg#: 3865463 posted 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