40000 Records only, Make my search page work so slow

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
hychanhan
Forum Newbie
Posts: 2
Joined: Sun Oct 29, 2006 8:29 pm

40000 Records only, Make my search page work so slow

Post 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
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post 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... :)
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post 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 :)
hychanhan
Forum Newbie
Posts: 2
Joined: Sun Oct 29, 2006 8:29 pm

40000 Records only, Make my search page work so slow

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Moved to Databases.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post by xpgeek »

To optimize you query use Explain.
[sql]Explain select ... from ....[/sql]
Post Reply