Is this the best way to run/write this INNER JOIN


#1

Hi All,

I would like to ask the GODs of MySQL here, if this is the best (fastest) way to write this INNER JOIN or is there a better (faster) way for thi:

SELECT ab.id, ab.client_id, ab.file_type, ab.banne_w, ab.banne_h, ab.max_per_click, ab.category, ab.url2link
FROM advertiser_banners AS ab
INNER JOIN paid_advert_users AS pau
ON (ab.client_id = pau.id) AND pau.user_status = 2
WHERE ab.category IN (2, 67, 909)
AND ab.online_stat = 'approved'
AND ab.banner_type = 'leader_board'
ORDER BY RAND() LIMIT 1

It seems to me like this is slower than it should be, as per MySQL browser fetch time.

So is there any way you could improve on this?

Thanks,
Dean


#2

there are many ways to write the same query

the optimizer will figure out which execution plan is the fastest

performance does ~not~ depend on how you write the query, but rather, on the availability of indexes

that said, try this --

SELECT ab.id
     , ab.client_id
     , ab.file_type
     , ab.banne_w
     , ab.banne_h
     , ab.max_per_click
     , ab.category
     , ab.url2link
  FROM advertiser_banners AS ab
 WHERE ab.category IN (2, 67, 909)
   AND ab.online_stat = 'approved'
   AND ab.banner_type = 'leader_board'
   AND EXISTS
       ( SELECT 'w00h00'
           FROM paid_advert_users AS pau
          WHERE pau.id = ab.client_id 
            AND pau.user_status = 2 )
ORDER 
    BY RAND() LIMIT 1

#3

So no INNER JOIN?

And what is 'w00h00' in
SELECT 'w00h00'
FROM paid_advert_users AS pau


#4

very perceptive... did you test it? did it work the same?

a placeholder

some people use NULL here instead


#5

This is unnecessarily complicating thins, aka making things more complex, and does not produce any measurable speed in execution. So I rather stay with JOIN statement. My question is then, staying with JOINs, is there any suggestion to speed up that SELECT?

Thanks,


#6

I would be interested in seeing the results of the EXPLAIN queries in comparison.


#7

what exactly are you referring to, please?

your JOIN query versus my EXISTS query are two ways of writing the SQL, and isn't that what you originally asked for?

so i offer another way to do it, and you dismiss it out of hand?

if i may ask, why??


#8

Yes, EXPLAIN is a good next step.
Except I need to study this EXPLAIN more. As I find its output well needing to be EXPLAINed :slight_smile:


#9

The use of:
AND EXISTS
( SELECT 'w00h00'
FROM paid_advert_users AS pau
WHERE pau.id = ab.client_id
AND pau.user_status = 2 )

rather than JOIN, is what I am referring to by level of complexity we want to avoid.
So basically my senses tell me that JOIN is preferred method for querying 2 Tables rather than us of the AND EXISTS.....
But thanks for your suggestion.


#10

So, let me understand. You want an experienced SQL programmer to say if there's a better way to code your query. You've got a couple of replies suggesting better ways, but yours was better all along? Or have I missed something?


#11

It can be a bit cryptic, one of those "don't understand until you understand" things.

For example, here's one of my older queries that could use improvement

mysql> EXPLAIN SELECT `countries`.`name` AS `country_name`
    -> , `states`.`name` AS `state_name`
    -> , `states`.`phoneme` AS `state_phoneme`
    -> , `states`.`transliteration` AS 'state_transliteration'
    -> , `states`.`transliteration_phoneme` AS 'state_transliteration_phoneme'
    -> , `cities`.`name` AS `city_name`
    -> , `cities`.`phoneme` AS `city_phoneme`
    -> , `cities`.`transliteration` AS 'city_transliteration'
    -> , `cities`.`transliteration_phoneme` AS 'city_transliteration_phoneme'
    -> FROM `countries`
    -> INNER JOIN `states`
    -> ON `countries`.`id` = `states`.`country_id`
    -> INNER JOIN `cities`
    -> ON `states`.`name` = `cities`.`state_name`
    -> WHERE 1 = 1
    -> ORDER BY `countries`.`name`, `cities`.`name`;
+----+-------------+-----------+------------+--------+---------------+---------+---------+-----------------------------+------+----------+---------------------------------+
| id | select_type | table     | partitions | type   | possible_keys | key     | key_len | ref                         | rows | filtered | Extra                           |
+----+-------------+-----------+------------+--------+---------------+---------+---------+-----------------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | cities    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                        |  575 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | states    | NULL       | eq_ref | PRIMARY       | PRIMARY | 302     | sitepoint.cities.state_name |    1 |   100.00 | NULL                            |
|  1 | SIMPLE      | countries | NULL       | eq_ref | PRIMARY       | PRIMARY | 1       | sitepoint.states.country_id |    1 |   100.00 | NULL                            |
+----+-------------+-----------+------------+--------+---------------+---------+---------+-----------------------------+------+----------+---------------------------------+
3 rows in set, 1 warning (0.25 sec)

See the "1 Warning" - notice the NULLs under "keys" ?
/* hint: databases like having keys.

See the "(0.25 sec)" ?
If this had been more than personal use localhost experimental code, it might have been a good idea to improve the query execution time. It wasn't, so I didn't care, so I didn't.


#12

1/ why do you see this as complex? maybe you don't understand it?

2/ you never acknowledged that i gave you a different query which is what you asked for

i'd like to see you in a professional job interview saying something like this

did you, in fact, actually test my suggestion?


#13

Thanks for this explanation of EXPLAIN.
I need to look into this in more details ASAP, but it is a hard to grasp component of MySQL. On that note, I was listening to one of the MySQL GODs, that is the chief designer at Percona and you probably know Percona is one of the leading MySQL dev sources, and he said that it took him 1 Year of studying the MySQL EXPLAIN before the light bulbs went on as to how exactly use this service. So that is in my self defense of still have not mastered this item


#14

Until you have completed the 1 year study just use the poor man's explain: run the two versions of the query on your database filled with data and see which one runs faster. Did you do this?


#15

Ya, I did that. Using MySQL Query Browser.
But I was wondering how accurate is that Fetch time that it reports back! Since of course this fetch time can be affected by other Queries and Processes that are running on the server at the same time. Right?


#16

Sure, other processes can affect the time but then you run the query many times and calculate an average. Make sure you bypass the query cache - if it's turned on you can use the SQL_NO_CACHE keyword in the SELECT. Also, for best results don't run the queries via remote connection because network latency can skew the results - run them directly from the server - for this you can use PHPMyAdmin or Adminer installed on the server and you will get pretty accurate times.


#17

the other nice thing about EXPLAINs is that they easily reveal whether indexes are being utilized properly for each query

this goes back to what i said in my very first reply (post #2) in this thread -- "performance does ~not~ depend on how you write the query, but rather, on the availability of indexes"


#18

LJ,
Thanks for this suggestion. We do not have PHPMyAdmin or Adminer installed, we completely Admin our servers via Putty. Will running the MySQL commands via Putty MySQL Admin, result in exact fetch times or is the time reported via Putty not the actual execution time but affected by latency between the actual server and the Putty being actually on the desktop? I think it is the latter, that is putty actually reports the fetch time on server, but I actually never thought about or read on this issue, so asking.


#19

Yes, Yes Indexes. Question is how to know which columns to put an Index on!
I mean of course you do not want to put an Index on every column.
OTN, do you have a best reading suggestion on how best to read EXPLAIN output and Index placement?


#20

yes... my suggestion is, you post both the query and the result of the EXPLAIN here, and we will, um, if you'll pardon the pun, explain them to you

nothing like learning a concept using your own data, eh