I've built a high profile photo website. After reaching 100,000 photos, my amateur abilities have began to appear and want honing, rapidly. Some simple queries take 5-ten seconds to come back!
I've got a one text-box search feature, that will search the "photos" table (for caption and headline), the "people" table, the "key phrases" table and also the "photoContributor" table. You will find two relational/association tables for "people" and "key phrases" known as "photoPeople" and "photoKeyword".
One possible user search maybe: "Kaira Pitt Julia Roberts Shades @MG" - This will return all photos which contain Kaira and Angelina together, where Shades is visible and brought through the contributor @MG.
I soon realized which i couldn't make use of an INDEX on my small "caption" and "headline" fields within the "photos" table because I'm while using "LIKE" clause having a "%" prefix, which individuals particular fields are going to a "LONGTEXT" data type. Because I've no indexes on individuals fields, it's leading to huge return occasions. Therefore, In my opinion I have to use "FULLTEXT" search, I'm able to then set the "caption" and "headline" fields to VARCHAR(2000), because the biggest caption to-date is 1991 figures, but still make use of the "INDEX" feature, that will hopefully quicken things. I additionally such as the boolean function to get rid of words in the search too.
The main reason I'm penning this question, is that i'm totally useless at "JOINS". I'm able to most likely write a "FULLTEXT" query on a single table and that i can most likely join two tables together utilizing a right or left join... but because I've the association/relational tables among, I recieve really, really confused.
I'd appreciate if somebody could please show me a good example query, using joins and full-text and taking advantage of relational tables, or simply show me which joins to make use of, if any, and then any tips you've for full-text around the database structure I've.
Below is my fundamental database schema:
photos (tbl) photoID INT(11) Primary Auto-Increment headline Lengthy-Text caption Lengthy-Text dateCreated DateTime people (tbl) peopleID INT(11) Primary Auto-Increment people VarChar(255) photoPeople (tbl) photoID INT(11) peopleID INT(11) key phrases (tbl) keywordID INT(11) Primary Auto-Increment keyword VarChar(255) photoKeyword (tbl) photoID INT(11) keywordID INT(11) photoContributor (tbl) photoID INT(11) contributorRef VarChar(100)
Whenever a search is created, the tables/fields which are queried are: photos.headline, photos.caption, key phrases.keyword, people.people, photoContributor.contributorRef.
I really hope somebody might help me going to construct this urgent query.
Getting both full text searches and foreign key contraints (which are great for joins) is a problem for MySQL because they are only based on MyISAM and InnoDB engines correspondingly.
To be able to make full text searches you would need to have tables towards the MyIsam engine. I do not have much experience there and so i cant assist you to much.
Time sink while you suspected, is you are utilizing as with a %prefix, forcing the database to check out every damn entry within the table before coming back.
If you wish to use joins, you'll have to separate the query string before polling the database, but that needs to be simple enough. A great summary of joins are available in the W3school lessons. http://www.w3schools.com/sql/default.asp
The primary problem i'm able to help you encountering is: Even when you get some decent joins implemented inside your database, you still want to use like around the became a member of tables (Since you cant do full text searches on InnoDB). Due to this making fancy joins won't accelerate your queries much.
My advise could be this: Make more search fields. Doing this will have the ability to obtain something good from joins. Split the individuals title up into First, middle surname to prevent requiring to make use of Like.
If you want to help keep the main one textfield search, you will need to place lower some conventions concerning how to enter data (So that you can split up behind the curtain and check) like you do having a prefix for contributor.
Im sorry which i cant become more precise and useful than this, but what you're showing isn't a quick-fix problem, im afraid.