Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
paparazzoKid
Starting Member
3 Posts |
 Posted - 2011-08-27 : 19:21:54
|
HiAre there any SQL gurus out there that can help me?I have a celebrity photo website, I currently have 90,000 photos in the database.I have recently found my old complicated search script has slowed down considerably. It was built by somebody else. It has over 2000 lines of code, using an "AND", "OR", "NOT" feature, so a user could search "personsname and personsname and keyword not keyword" in to one textbox.I have been advised by a developer to use full-text search and that my code should not be on 2000+ lines of code, it can all be done in one SQL query! This is fine, I can do this if I had one table to query, but I have a few tables to query and I don't understand joins yet, and this is soooo urgent as my search box hardly works.I'm not that bothered about the and, or and not feature, I would like all words to match.This is my database schema. Is there anybody out there that could help me produce a SQL query for this? It's worth a few coffees sent by PayPal...photos (tbl)photoID INT(11) Primary Auto-Incrementheadline Long-Textcaption Long-TextdateCreated DateTimepeople (tbl)peopleID INT(11) Primary Auto-Incrementpeople VarChar(255)photoPeople (tbl)photoID INT(11)peopleID INT(11)keywords (tbl)keywordID INT(11) Primary Auto-Incrementkeyword VarChar(255)photoKeyword (tbl)photoID INT(11)keywordID INT(11)photoContributor (tbl)photoID INT(11)contributorRef VarChar(100) An example search could be:"Brad Pitt Angelina Jolie Sunglasses @MG"This should return all photos that contain Brad Pitt and Angelina Jolie where a pair of Sunglasses can be seen and the photos were taken by contributor @MG. This would query the People tbl, Keyword tbl, photoContributor tbl and Photos tbl.As I said, I'm happy to PayPal some coffees to the helper. |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-28 : 03:44:39
|
| "I have been advised by a developer to use full-text search and that my code should not be on 2000+ lines of code"Ah ... a developer with a magic bullet eh? Probably able to give you that wonderful advice without even examining the code in detail ...Your current code may well be inefficient. Changing-horses to Free Text searches will bring its own set of problems - will your users just get an error if they put words like "The" in your search box? You will have to do regression tests to prove that all types of searches, that currently work, will work with the new code - and give the same results (or better). That's a lot of work - tuning your query, if done with care, will not change the logic and thus the testing effort will be much reduced and much lower "risk"My experience of free text search on MULTIPLE fields was that its performance was not brilliant.."I'm happy to PayPal some coffees to the helper."That's kind, but we're happy to help for free. Although we may not be around enough, or responsive enough, compared to hiring a Database Expert.Now then ... do you know if any maintenance is done on your database? Are the Indexes and Statistics rebuilt often? (and you do have a regular backup? that won't effect performance though ...)Over what period has the performance fallen off? What has changed in that time? Has the amount of data increased dramatically? If not some housekeeping may well be enough to keep you going for the time being. Have other applications been added to the server? (is it a shared server?)Has the application code changed much?Lots of ANDs and ORs in your query is problematic for performance. But can be worked around. There are Performance Tools in SQL Server that enable you (or us) to see how your query is performing, which indexes it is using (or not) and so on. From that information queries can be "tuned" - if its written in a very basic way then it is entirely fesible for performance to improve by a factor of 100xAre you skilled enough to tinker with the SQL Query? (If not I guess you will need a Database Developer who can sort it out for you, but if you are up for the challenge I would expect that the folk here can solve it).Just to double check: your database is a Microsoft SQL Server database? (its just that your schema "Long-Text" and "Auto-Increment" are not terms that I normally see is MS SQL tools) |
 |
|
|
paparazzoKid
Starting Member
3 Posts |
Posted - 2011-08-28 : 08:11:01
|
| I'd like my search to be the same as Getty Images. If you visit: gettyimages.co.uk/EditorialImages/Entertainment and try to search for "Brad Pitt Angelina Jolie Sunglasses", you will notice that on page 1 the results are spot on... but as you get to page 12 the results are slightly less relevant.Another Getty search would be "Chloe Green". Up to page 3 the results are spot on, then it moves on to a girl called "Mickey Green", that has "Chloe" written in the caption somewhere, and then it goes back to "Chloe Green" results again. Also, as you say, you cannot just type in "that" or "at" or "in", it will return an error - I am happy with this.At the moment, no maintenance is carried out on my DB. I don't know what indexes and statistics are, let alone rebuilding them and yes, I do take a weekly MySQL backup - which will be daily soon. This is done by using Windows Schedule, it runs an ASP page which dumps all tables in to a text file.The search script worked fine up to around 4000 records, I then stopped testing it as it *seemed* to work fine. Nothing in the application or database has changed apart from going from 4000 to 100,000 records and now it has slowed considerably.I am using just MySQL.If you think that full-text search over multiple tables won't perform well enough, can you suggest a method that might? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-28 : 11:30:00
|
| This forum is for Microsoft SQL Server, so you may not find anyone here who knows about MySQL.I'm afraid I don't know whether rebuilding indexes and so on (housekeeping) is necessary on MySQL. I also don't know if MySQL has a free text search tool (it probably does, or some 3rd party add-on).It is possible that if you have gone from 4,000 to 100,000 records that you have gone "past" some threshold where your searching algorithm is going to perform fast enough.You might well be better off building your own keywords system - splitting columns with searchable text into individual words, using a Synonyms table to make sure that only root words are stored (so all plural words will be stored as the singular, and so on). This would give you a lot of agility to know that "Brad Pitt Angelina Jolie Sunglasses" refers to "Brad Pitt" and "Angelina Jolie" (and therefore also "Sunglasses"), rather than getting hits on "Pitt Angelina" and "Jolie Sunglasses" - assuming that those phrases existed in your data [facetious example ...]Has Google indexed your whole site? If you put:site:YourDomainName.com Brad Pitt Angelina Jolie Sunglassesinto Google does it give you good hits?(I'd then be curious to know ifsite:YourDomainName.com "Brad Pitt" "Angelina Jolie" Sunglassesgave you better hits ... but that's a refinement)If Google works well you could replace your current search by a "Google Custom Search" - this site uses one, the box is top-right of the page. You might need to do some pre-processing of the user's search tect to convert "@MG" into something that Google will uniquely find on your pages "Uploaded by MG" or something like that perhaps. |
|
|
|
paparazzoKid
Starting Member
3 Posts |
Posted - 2011-08-28 : 13:34:32
|
| Looks like I posted in the wrong forum, will try and find one that deals with MySQL - I thought this one did, obviously misread.Google hasn't indexed out whole site, only the home page and contact page. The pages that display photos and videos are for authorized members only, such as newspapers and magazines that want to download celebrity media. It's all password protected. |
|
|
|
|
|
|
|
|
ASP, SQL, Full-Text Search