Jump to content


Photo

Union selecting, but is there a quicker way?


  • Please log in to reply
5 replies to this topic

#1 renwoshin

renwoshin
  • Members
  • PipPip
  • Member
  • 14 posts

Posted 27 December 2005 - 08:18 AM

Hi,

I was wondering how you could search through all the entries in the tables in the database and have a complete list all in one query. The only way I know how to do is this:

$request = "SELECT * FROM cp WHERE email='" . $_SESSION['email']. "' " .
"UNION SELECT * FROM dv WHERE email='" . $_SESSION['email']. "' ORDER BY log DESC";
.
.
.

and keep on adding the "union select" for all 10 tables I have. All 10 tables have the same column names.

Is there a quicker way to search for one thing through the whole database? Thanks a bunch.

#2 fenway

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

Posted 27 December 2005 - 03:12 PM

Not really... if they're in separate tables, you have to run through each one; in principle, you could generate a view in MySQL 5 if you're planning on doing this very often. Furthmore, make sure that your ORDER BY clause applies to the result set _after_ all of the UNIONs -- if you have subselect support, you can easily do this enclosing the entire set of 10 tables + UNION statement in parenthesis, and then issue the order by on the derived table. Otherwise, the parser might get confused. Lastly, if you have do this for many queries, this may be an indication that all these data should reside in a single table with a type flag.

Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 renwoshin

renwoshin
  • Members
  • PipPip
  • Member
  • 14 posts

Posted 27 December 2005 - 06:23 PM

[!--quoteo(post=330654:date=Dec 27 2005, 07:12 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Dec 27 2005, 07:12 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
Not really... if they're in separate tables, you have to run through each one; in principle, you could generate a view in MySQL 5 if you're planning on doing this very often. Furthmore, make sure that your ORDER BY clause applies to the result set _after_ all of the UNIONs -- if you have subselect support, you can easily do this enclosing the entire set of 10 tables + UNION statement in parenthesis, and then issue the order by on the derived table. Otherwise, the parser might get confused. Lastly, if you have do this for many queries, this may be an indication that all these data should reside in a single table with a type flag.

Hope that helps.
[/quote]


Thanks for the reply man. I guess I will have to design my database differently.

#4 fenway

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

Posted 27 December 2005 - 08:41 PM

I guess I have the same question for you as I had for another poster -- what's the basis for keeping the table separate? If it doesn't help, and make thing easier, then you've added unnecessary duplication of fields among tables, and you run into the types of problems you've discovered.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 renwoshin

renwoshin
  • Members
  • PipPip
  • Member
  • 14 posts

Posted 28 December 2005 - 12:05 AM

[!--quoteo(post=330752:date=Dec 27 2005, 12:41 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Dec 27 2005, 12:41 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I guess I have the same question for you as I had for another poster -- what's the basis for keeping the table separate? If it doesn't help, and make thing easier, then you've added unnecessary duplication of fields among tables, and you run into the types of problems you've discovered.
[/quote]

oh ok. I just started using mySQL and i have no idea how it searches the table when you query for something. I kept them separate because by using an if statement, I could choose which table to search from. Since mySQL HAS to go through all the elements, if I separated them out, each query would just have to go through 300 rows instead of 3000 rows.

Just trading space efficiency for time efficiency, but I'm guessing the time that I save is negligible?

And btw, I'm that other poster you're talking about. Thanks.

#6 fenway

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

Posted 28 December 2005 - 12:22 AM

The "time efficiency", as you call it, would come from your WHERE clause, that would normally restrict the SELECT queries to just a particular subset of records, e.g. "WHERE type = 'cv'" or "WHERE type = 'dv'", to use your table name as examples of a flag you could set to mark the records. In this fashion, using a proper index on this column, you would be able to pull just the correct records which at the same speed (still only the 300, not the 3000), but if you needed to get the all (or groups of them), you could do it rather easily -- e.g. "WHERE type IN ('cv','dv')" -- without any UNION statements.

Hope that makes sense -- almost all searches are done using an index, and there are many resources you can consult if you're having trouble with those concepts (including this forum!). I can give many more examples that are specific to your particular scenario if need be.

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