Jump to content

MySQL JOIN optimize


alpine

Recommended Posts

I have so far 4 identical tables that i query like this
[code]
mysql_query("(SELECT * FROM $table_10 WHERE MATCH(itemno) AGAINST('$s*' IN BOOLEAN MODE))
UNION ALL (SELECT * FROM $table_11 WHERE MATCH(itemno) AGAINST('$s*' IN BOOLEAN MODE))
UNION ALL (SELECT * FROM $table_12 WHERE MATCH(itemno) AGAINST('$s*' IN BOOLEAN MODE))
UNION ALL (SELECT * FROM $table_13 WHERE MATCH(itemno) AGAINST('$s*' IN BOOLEAN MODE))
ORDER BY itemno,price limit $limit") or die(mysql_query());
[/code]

Can this be optimized to return faster than with my query?
The reason for it being 4 separate tables is due to different times for new issues to update as the tables are sorted on different suppliers of mine. So putting them in one table is not an option at this time.
The query is not slow, but it would not hurt for it to be slightly faster as i use ajax to query results.
The total rows of all four tables is approx 400.000, and i have a index on itemno

Thanx for any helps :)
Link to comment
Share on other sites

Well, not having seen the explain, I can't be certain, but I would guess that the FULLTEXT search is the slowest part.  Mind you, since you're using a UNION, the ORDER BY will incur a temporary/filesort.  The only other way to handle this would be to create a VIEW of these tables sorted in the correct order already -- that way, you're only doing a single FULLTEXT search operation.
Link to comment
Share on other sites

Thanks for your reply, i'm not sure i understand exactly what you mean by creating a VIEW - do you mind giving me a small example ?

*edit* I looked up on MYSQL VIEW and puzzled with it, but i just can't seem to get the hang of it...

This is what i have and its working but it's extremely!! slow
[code]
$view = mysql_query("CREATE VIEW goods AS (SELECT * FROM $table_10)
UNION ALL (SELECT * FROM $table_11)
UNION ALL (SELECT * FROM $table_12)
UNION ALL (SELECT * FROM $table_13)
ORDER BY itemno,price limit $limit") or die(mysql_error());

$result = mysql_query("SELECT * FROM goods WHERE MATCH(itemno) AGAINST('$s*' IN BOOLEAN MODE)") or die(mysql_error());
[/code]

On second search i get error "table goods already exists" so i tried a CREATE OR REPLACE VIEW making it very slow altogether..

So i put back my original query again. I'm sure i miss out something regarding VIEW

Btw- the query can be found live here to test speed --> [url=http://www.elektrostart.no/multiplukk/]http://www.elektrostart.no/multiplukk/[/url] (type e.g. 10348 or 13173)
Link to comment
Share on other sites

I could have checked that myself using my Mysql Turbo Manager, just wasn't thinking *lol* - sorry about that

OK - with that in mind i created a full VIEW once ordered as above and tested again, only with the LIMIT in final select query.
But it is slow, i havent bothered to measure exact time, but my original query using UNION ALL on four tables takes about 0,5-1 sec to display full results while one select on the VIEW table takes roughly 3-4 sec to display the same results. Is it any INDEX that is failing in VIEW here ?
Link to comment
Share on other sites

well, i'm happy with my current union query and now i have explored the possibilities. The best about it is that i have learned yet another thing that does come in handy in some situations - the VIEW.

Thanks a bunch for your splendid help fenway!
Link to comment
Share on other sites

Just an update as i made a small progress by adding INDEX on the two cols i am ordering by, this actually did a difference in speeding up :)
So now i have both a KEY and a FULLTEXT KEY - the KEY on the ORDER BY cols and a FULLTEXT on those cols used in search-query.

[code]
mysql_query("ALTER TABLE tablename ADD INDEX indexkeyname (col_itemno,col_price)") or die(mysql_error());

mysql_query("ALTER TABLE tablename ADD FULLTEXT fulltextkeyname (col_itemno,col_description)") or die(mysql_error());
[/code]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.