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
40000 Records only, Make my search page work so slow
Moderator: General Moderators
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
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.
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
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
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
=========================================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
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact: