Jump to content


Photo

MySQL JOIN optimize


  • Please log in to reply
12 replies to this topic

#1 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 10 August 2006 - 02:23 PM

I have so far 4 identical tables that i query like this
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());

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

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 10 August 2006 - 02:58 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 10 August 2006 - 05:09 PM

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
$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());

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 --> http://www.elektrostart.no/multiplukk/ (type e.g. 10348 or 13173)

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 August 2006 - 04:03 PM

Well, you only need to create the view once; but I'm surprised that it's slower.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 11 August 2006 - 05:47 PM

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...

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 11 August 2006 - 07:26 PM

No, it's "permanent", unlike a temporary table...
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 12 August 2006 - 08:07 AM

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 ?

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 August 2006 - 02:55 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#9 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 12 August 2006 - 03:53 PM

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!

#10 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 12 August 2006 - 06:14 PM

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.

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());


#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 August 2006 - 06:19 PM

Yup... a covering index would definitely help.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#12 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 12 August 2006 - 06:36 PM

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 ?

#13 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 13 August 2006 - 03:03 PM

Well, post the EXPLAIN output, and you'll what indexes are being utilized.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users