How do I combine 2 sql statements together into a single query in mysql?

Question

I have two complex-ish sql statements. One of them generates a count and the other queries for a leaderboard, which has different columns. In the php script I choose only one row out of the second returned query, and then combine the count from the first query result and return it to the caller.

The problem: since I am making two queries to the database, this significantly increases the load time. I would like to combine the two queries to optimize the call.

Here are my queries:

select count(*) as leaderboard_entry_history_tally 
from leaderboard_entry 
natural join user 
natural join leaderboard where 
(leaderboard_load_key = 'fr-en-colors' or leaderboard_load_key = '-fr-en-colors' )
and leaderboard_quiz_mode = '0' and user_id = 37

set @t1=0; select
NOW()-leaderboard_entry_timestamp as leaderboard_entry_age_in_some_units , 
TO_DAYS(NOW())-TO_DAYS(leaderboard_entry_timestamp) as leaderboard_entry_age_in_days , leaderboard_entry.user_id , 
leaderboard_entry.leaderboard_entry_id , 
leaderboard_entry.leaderboard_id , 
leaderboard_entry.leaderboard_entry_timestamp , 
leaderboard_entry.leaderboard_entry_friendly_load_key , leaderboard_entry.leaderboard_entry_session_uuid ,
leaderboard_entry.leaderboard_entry_num_mistakes , 
leaderboard_entry.leaderboard_entry_num_unique_cues , 
leaderboard_entry.leaderboard_entry_latitude ,
leaderboard_entry.leaderboard_entry_longitude , 
leaderboard_entry.leaderboard_entry_app_market_code , 
leaderboard.leaderboard_quiz_mode , 
leaderboard.leaderboard_load_key , 
leaderboard_entry.leaderboard_entry_elapsed_time_ms 
from leaderboard_entry 
natural join leaderboard 
where (leaderboard_load_key = 'fr-en-colors' or leaderboard_load_key = '-fr-en-colors' ) 
and leaderboard_quiz_mode = '0' 
and user_id in (37) 
order by leaderboard_entry_timestamp desc limit 0, 1

I have looked through other posted solutions. Some of these use temporary tables, which didn't seem to work when I tried it (got syntax errors).

I also tried nested queries, but couldn't get this to work even with simple stuff like the following:

select 
(
select count(*) from leaderboard_entry
) , * from leaderboard_entry_timestamp

Any help would be much appreciated!

Thanks swine

tidy up your sql - learn about aliasing and can you refrain from using natural joins (think why) thanks ever so much. — Jon Black
– Jon Black, Commented Mar 9, 2011 at 5:34
i'm an expert c++ dev, but a noob when it comes to sql. i've googled around but didnt find much that says sql natural joins are bad in this case (i'm natural joining on indexes). can you elaborate on your statement and perhaps provide links? thanks — swinefeaster
– swinefeaster, Commented Mar 9, 2011 at 5:53

criel · Accepted Answer · 2011-03-11 16:35:51Z

3

If speed is your problem then I'd make sure that you have foreign keys and their indexes defined in your schema. Also make sure you have indexes on all the criteria items you search by.

Not sure on the syntax (I'm an Oracle guy) but maybe something like this:

How to add foreign keys:

ALTER TABLE leaderboard_entry ADD FOREIGN KEY (leaderboard_id) REFERENCES leaderboard(leaderboard_id);

How to add indexes:

alter table leaderboard_entry add index using hash(leaderboard_id);

Also, using prepared statements and bind variables can help too. It will eliminate the parsing step once it is done once.

Here's a link describing how to do this:

http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

See the section "Prepared Statements".

answered Mar 11, 2011 at 16:35

criel

1381 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zerkms · Accepted Answer · 2011-03-09 05:25:11Z

1

The problem: since I am making two queries to the database, this significantly increases the load time.

It is not true.

You have bad performance because your queries aren't optimized well (or at all). So stop thinking that 1 query is faster than 2.

Also - you cannot combine those 2 queries since they return different number of columns.

answered Mar 9, 2011 at 5:25

zerkms

256k73 gold badges447 silver badges551 bronze badges

3 Comments

swinefeaster Over a year ago

Any thoughts on how to optimize them? Thanks

zerkms Over a year ago

@swinefeaster: remove all unnecessary from queries at first

swinefeaster Over a year ago

there are no unnecessary from queries

Collectives™ on Stack Overflow

How do I combine 2 sql statements together into a single query in mysql?

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related