MySQL JOIN optimize

[alpine]

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 :)

[fenway]

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.

[alpine]

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)

[fenway]

Well, you only need to create the view once; but I'm surprised that it's slower.

[alpine]

But the view isn't stored permanantly as a new mysql-table is it? I was under the impression that it's stored in memory only...

[fenway]

No, it's "permanent", unlike a temporary table...

[alpine]

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 ?

[fenway]

Sorry, my bad -- I forgot that using UNION will negate the possibility of using the MERGE algorithm, which means that the underlying index can't be used.  I can't really think of a way around this at the moment.

[alpine]

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!

[alpine]

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]

[fenway]

Yup... a covering index would definitely help.

[alpine]

I take it that both my indexes is needed, just the FULLTEXT / or the INDEX would not do handling both the ORDER BY and select WHERE clause ?

[fenway]

Well, post the EXPLAIN output, and you'll what indexes are being utilized.