Page 1 of 1

40000 Records only, Make my search page work so slow

Posted: Sun Oct 29, 2006 8:37 pm
by hychanhan
Dear All,

(PHP/MySQL)

In my table have record more than 40000 rocords (all are pictures)and i have a search page when i start to search it work so slow and stuck, sometime the time is out.

Do you have any idea to help me? please help me.
Thanks you alot.

From:
Chanhan

Posted: Sun Oct 29, 2006 9:07 pm
by alex.barylski
It's to my understanding that keeping a BLOB in the same table as search fields will impact retreival times significantly...

Make sure you use indexes...optimize those queries & normalize your database...

What does your schema look like and let's see the query string... :)

Posted: Sun Oct 29, 2006 9:17 pm
by s.dot
If you haven't indexed it, do so. As a general rule, index the field that you do most of your WHERE clauses on

SELECT * FROM `table` WHERE `foo` = '$bar'

You would index foo.

Posted: Sun Oct 29, 2006 10:13 pm
by alex.barylski
You know scottayy, I just learned about that the other day...I had no knowledge of what indexes really were...outside of the fact I thought primary keys were indexes - which I believe is still correct...

After learning that, I sat there thinking...you know what would be a neat feature in a DB abstraction layer like AdoDB maybe...a logging feature where fields used to search were recorded...and over time AdoDB could use this log data and hueristically re-adjust indexes to optimize queries :)

Cheers :)

40000 Records only, Make my search page work so slow

Posted: Sun Oct 29, 2006 10:37 pm
by hychanhan
Hockey wrote:It's to my understanding that keeping a BLOB in the same table as search fields will impact retreival times significantly...

Make sure you use indexes...optimize those queries & normalize your database...

What does your schema look like and let's see the query string... :)
=========================================
Statement below make my search so slow:

$sWhere = "c.zipcode =". $searchresultstr;
$sWhere = " WHERE (" . $sWhere . ")";
$sWherePlus="AND (p.isbrand=True) AND (p.category_id=1 OR p.category_id=3 OR p.category_id=5 OR p.category_id=6 OR p.category_id=8 OR p.category_id=9 OR p.category_id=13 OR p.category_id=25 OR p.category_id=36)";


$sql_ads="SELECT DISTINCT t.contact_id as t_contact_id FROM `osb_contacts` AS `t`
Inner Join `osb_companies` AS `c` ON `t`.`company_id` = `c`.`company_id`
Inner Join `osb_products` AS `p` ON `t`.`company_id` = `p`.`company_id` WHERE ((POW((69.1*(c.longitude-"$lon")*cos($lat/57.3)),"2")+POW((69.1*(c.latitude-"$lat")),"2"))<($radius*$radius)) $sWherePlus";

$sSQL1="SELECT DISTINCT p.brand_sub_id as p_brand_sub_id FROM (osb_companies c INNER JOIN osb_company_info_details cd ON c.company_id = cd.company_id) INNER JOIN osb_products p ON cd.brand_sub_id = p.brand_sub_id WHERE ((POW((69.1*(c.longitude-"$lon")*cos($lat/57.3)),"2")+POW((69.1*(c.latitude-"$lat")),"2"))<($radius*$radius) $sWherePlus)";

------------------------------------------
The idea that i want:

if p.category_id=1 or p.category_id=8 then $radius=50

the other else $radius=1000

Can i use condition if in Where statement of MySQL and Can i set Variable of PHP
in Where Statement of MySQL

Please help me

Posted: Sun Oct 29, 2006 11:25 pm
by John Cartwright
Moved to Databases.

Posted: Mon Oct 30, 2006 1:29 am
by timvw
- If your dbms supports it, you could rewrite your query using a subquery instead of joining over three (large) tables..

- Since you're feeding the query $radius * $radius, you might want to calculate $quare_radius and use that in the query...

Posted: Mon Oct 30, 2006 4:52 pm
by xpgeek
To optimize you query use Explain.
[sql]Explain select ... from ....[/sql]