PHP Loading times

Question

The following query take 10.86secs to initiate,

$sql="SELECT items.id i_id, status,manufacturerid,model,label,cpuno,corespercpu 
      from items,item2soft 
      where item2soft.itemid=items.id AND item2soft.softid={$r['id']} 
      order by label asc ";

While this code takes 23.73secs

$sql="SELECT items.id i_id, status,manufacturerid,model,label,cpuno,corespercpu 
      from items,item2soft 
      where item2soft.itemid=items.id AND item2soft.softid={$r['id']}";

The only difference between two codes is the latter has a ORDER BY keyword.Is there any way to make it faster.Please feel free to ask me anything.thanks for your help :)

Do an EXPLAIN for the query against your database, look to see what (if any) indexes are being used — Mark Baker, Sep 25 '13 at 15:32
**By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. — Andy Lester, Sep 25 '13 at 16:27

score 2 · Answer 1 · answered Sep 25 '13 at 15:34

2

After looking at your query - and by that I mean: adding proper indenting so I can actually read it - you probably just need to add some indexes.

$sql = "SELECT
    items.id i_id,
    status,
    manufacturerid,
    model,
    label,
    cpuno,
    corespercpu
FROM
    items,
    item2soft
WHERE
    item2soft.itemid = items.id
    AND item2soft.softid = {$r['id']}
ORDER BY label ASC"

Add indexes on item2soft.itemid and item2soft.softid

If it's still slow, run an EXPLAIN

answered Sep 25 '13 at 15:34

Halcyon

57,230
10
89
128

Thanks Frits, I'm new to this PHP SQLite deal.Can you please tell me a way to add an index to existing tables. – Carter Sep 25 '13 at 15:38
If the slowdown occurs when adding the ORDER BY clause, I'm guessing that whichever table holds that column needs a compound index... – Neville Kuyt Sep 25 '13 at 15:38
@Carter use Adminer or PHPMyAdmin to get a good view on your database. They have a nice GUI to add indexes. – Halcyon Sep 25 '13 at 15:40
Thanks Frits,If i manage to create an index, how would I implement it?.as in do i have to change the table name in the query. – Carter Sep 25 '13 at 15:44
No you wont need to change anything. indexes allow MySQL to perform certain JOINs faster. If you want to know the specific you'll have to read up on it, it's fairly complex. – Halcyon Sep 25 '13 at 15:49
Yay it works!!thanks guys..now with order by it only takes 11secs.is there any other way to make this faster.(special thanks to Frits) – Carter Sep 25 '13 at 15:50
Well, for starters you could add a LIMIT. I'm guessing your table is really big (like 100.000 records), no way you need all 100.000. Optimizing ORDER BY is hard, especially if it's a string type field. You could add an index but those are kind of expensive (they take a lot of space). – Halcyon Sep 25 '13 at 15:53

PHP Loading times

1 Answers1