

Ask HN: Can you help me optimize this query? - throwaway1933

i have 2 tables, one for users and one for articles. each of them have around 700,000+ records.<p>this is the query i'm using when people do a search:<p>"select article_id,caption,articles.description,owner_id,category,users.username, users.type from articles JOIN users ON owner_id=user_id WHERE MATCH(caption, tags,article.description) AGAINST ('+$criteriaBoolean' IN BOOLEAN MODE)"<p>i've setup the following indexes. for the article table i setup owner_ix, category_ix as KEY and articles_fullix as FULLTEXT.<p>also in the user table i setup user_ix as KEY and username as FULL TEXT.<p>this is running on a dedicated MySQL server. Any idea how to optimize it? this seems to be the slowest loading page on my site.<p>thanks
======
Johngibb
You should really ask this on stackoverflow, you'll have much better luck.
This isn't really the place for this sort of question.

