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