1. Home
  2. Forums Index
  3. Server Side / Databases 2:53 am May 21, 2026

Forum Moderators: open

Message Too Old, No Replies

MySQL Match Against + a very expensive join. How do I optimize this?

This seems like an impossible problem

         

httpwebwitch

1:02 am on Nov 12, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



oy vey, do I ever have a problem.

I have one table - called "items", with about 800,000 rows. In it are various data specs about products.

Each product can be described in multiple languages, so i have another table - called itemtext - which uses items.id as a key plus a "language" column - those two make up a unique index - plus two more columns for the product name and description of that item in that language.

I used to have this search function that used a Fulltext index in the items table; that was when I had the name and description in that table. It was slow, but tolerable, usually returning results in 1 to 3 seconds.

Now you'd suppose that it would be easy to apply the fulltext index to the itemtext table, and it is. But my problem is that there are columns in `items` that also need to be in the WHERE clause, notably: deleted (indicating that a product is queued for deletion), active, and some other flags that affect whether a product should be visible in the search.

What I end up with is a query like this:

SELECT itemtext.*,
MATCH (itemtext.name,itemtext.description) AGAINST ('word') as score
from items,itemtext
WHERE items.id = itemtext.itemid
AND MATCH (itemtext.name,itemtext.description) AGAINST( 'word' IN BOOLEAN MODE)
AND items.active = 1
AND items.deleted = 0
ORDER BY score DESC

I may have the syntax wrong there.. I'm recalling it from memory (the kind in my head, not the kind that crashed when I performed that query)

Now, as I was writing it, I knew this was going to be a nastily expensive query. It's already using a fulltext index - it has to - but furthermore it's doing a huge JOIN between items (800K rows) and itemtext (over 1M rows). Since any particular query can only use one index at a time, you can guess that the join, even though it's on a primary key, is going to be insane.

Indeed, when I tried it, MySQL crashed. It crashed slowly and agonizingly, over 5 minutes of despair and anguish. It could only be resuscitated with a KILL and RESTART.

I need a viable search option for this site. But because of this JOIN, the fulltext index is probably not going to be possible.

What are my options?

Are there any good denormalizing solutions that will provide search functionality without relying on a real-time query of the two tables?


reluctantly, I could maintain a duplicate, pre-filtered, itemtext table where it only includes rows where items.deleted = 0 and items.active = 1... that would work and it would denecessitate the JOIN. But how do I do that? must I write this secondary index maintenance into the application code?

DWarp9

10:03 am on Nov 12, 2010 (gmt 0)

10+ Year Member



Well, it seems to me the query-optimizer should already be doing this, but since it crashes I'm gonna go out on a limb and suggest it anyway:

How about putting your fulltext search in a nested query, and do a limit on the number of rows return (Assuming you want to page your search results), and THEN perform the joined. Then you would only have to join X rows with your 800k items...

Now, as I said, the query-optimizer might already be doing this, and you'll need another approach, but just the same, I think it's worth a try.

Hope it helps
-Peter

httpwebwitch

12:14 pm on Nov 12, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



DWarp9, thanks!

I tried something similar and it works great.

query #1: 

select distinct itemtext.itemid,
MATCH (`name`,`description`) AGAINST ('".mysql_real_escape_string($q)."') as score
FROM itemtext
WHERE MATCH(`name`,`description`) AGAINST ('".mysql_real_escape_string($q)."' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 500



then I put those results in an array named $temp

query #2: 

SELECT itemid 
FROM items 
WHERE itemid IN (".implode(",",$temp).")
AND deleted = 0
AND active = 1


The "WHERE itemid IN" feeds the temp results in, the other two clauses rid the list of those ones that are deleted or inactive.

then I cache that result and use it to paginate without making the query repeatedly.

The result is that I don't necessarily get a 500 hits, but I can cope with that emotionally.
Importantly they're filtered the way I want, still ordered by their score, I can easily paginate the list, and the queries are fast.

DWarp9

1:35 pm on Nov 12, 2010 (gmt 0)

10+ Year Member



Glad to hear it!

You could save yourself a round-trip to the db by nesting the first statement like so:
SELECT itemid
FROM items
WHERE itemid IN
(select distinct itemtext.itemid,
MATCH (`name`,`description`) AGAINST ('".mysql_real_escape_string($q)."') as score
FROM itemtext
WHERE MATCH(`name`,`description`) AGAINST ('".mysql_real_escape_string($q)."' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 500)
AND deleted = 0
AND active = 1


If you want a little more flexibility, assuming you have a variable $pagenum holding the current pagenumber, you could do the limit like this:

LIMIT ".($pagenum-1).", ".$pagesize


It might be slower, since you don't cache the query, but you could probably work around that as well.

httpwebwitch

6:11 pm on Nov 14, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Oops
I found after a couple of test searches that the ORDER of relevance is lost when I do that "WHERE IN" query. The results come back in primary key order, not in order of the items in the "IN" clause.

I fiddled with it a little last night, and found that doing a weighted match against `name` and `description` gave better results. This way the word frequency in the name counts for more than in the description.

The new query uses a temporary table instead of an array in PHP-space.

It looks like this:

select items.itemid, serps.score from
items,
(select distinct itemtext.itemid,
1.5 * (MATCH (`name`) AGAINST ('".mysql_real_escape_string($q)."'))
+ 0.5 * (MATCH (`description`) AGAINST ('".mysql_real_escape_string($q)."')) as score
FROM itemtext
WHERE MATCH(`name`,`description`) AGAINST ('".mysql_real_escape_string($q)."' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 15000) AS serps
where serps.itemid = items.itemid
AND items.deleted = 0
AND items.active = 1
ORDER BY score DESC;


The results are good! some sample searches came up with nice relevant results, it's quick, and the caching makes all subsequent searches and pagination very fast.

httpwebwitch

2:30 pm on Nov 15, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



the trick is the temporary table named "serps" being built within the query.

In my first attempt, I was actually joining the two tables first, then filtering that JOIN using a WHERE clause. It's no wonder it was inefficient.

In the current version (above) the `serps` temporary table only has matching items in it, so JOINing that into the items table is a lighter operation - it only joins as many rows as there are matches. The weighted scores on the two columns do a better job of sorting relevant matches. It's done with one query, instead of two, and doesn't require an array to store those temporary matches. Query optimizer does a rather good job on this one.

A lot better than what I started with.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / Databases 2:53 am May 21, 2026