sql optimizations

Hi there:

We have a company doing mysql and code optimization on our site. They've made the following observations and recommendations, which we will implement in our code (and continue to test). I am submitting it here for your review, and perhaps inclusion in future builds. If you find this info helpful, please let me know and I will continue to post more info as we progress with our optimizations.

Thanks!

----

SELECT count(id) FROM jos_uddeim WHERE (totrashdate>=1208990440 AND
toid=64854 AND totrash=1) OR (totrashdateoutbox>=1208990440 AND fromid=64854 AND totrashoutbox=1 AND toid<>fromid AND ((systemmessage IS NULL) OR
(systemmessage='')))

This for query MySQL has problems choosing the right index and finally decides to go with table scan. I rewrote it to this:

SELECT SUM(id) FROM (SELECT count(id) id FROM jos_uddeim WHERE (totrashdate>=1208990440 AND toid=64854 AND totrash=1) UNION SELECT
count(id) id FROM jos_uddeim WHERE (totrashdateoutbox>=1208990440 AND
fromid=64854 AND totrashoutbox=1 AND toid<>fromid AND ((systemmessage IS
NULL) OR (systemmessage='')))) _jos_uddeim

which should work much faster and with less stress on the database. There could be better indexing for it too, but I'll leave it for now to see what other changes may be, so you would not modify the indexes constantly unless it's absolutely required.


----

# Time: 080424 17:14:42
# User@Host: [removed] @ localhost [] # Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 63488 SELECT count(a.id) FROM jos_uddeim AS a LEFT JOIN jos_users AS b ON a.toid=b.id WHERE a.totrashoutbox=0 AND ((a.systemmessage IS NULL) OR
(a.systemmessage='')) AND a.fromid=45834;

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 63604
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: [database].a.toid
rows: 1
Extra: Using index

jos_uddeim lacks index on `fromid` column.

REMEDY: Add index to fromid column

p.s. - I'm having a hard time finding the code that produces that first query...

Any hints as to where it lies?

Nevermind - looks like lines 46 or 72 in trashcan.php?

Post edited by: roverradio, at: 2008/04/25 03:31

Hi, don't worry with 1.0 code, check 1.1:

[code:1] $sql = "SELECT a.*, ufrom.".($config->pubrealnames ? "name" : "username"«»)." AS fromname,
uto.".($config->pubrealnames ? "name" : "username"«»)." AS toname
FROM (#__uddeim AS a LEFT JOIN #__users AS ufrom ON a.fromid = ufrom.id)
LEFT JOIN #__users AS uto ON a.toid = uto.id
WHERE (totrashdate >= ".$timeframe." AND a.totrash=1 AND a.toid =".(int)$myself."«»)
OR (totrashdateoutbox >= ".$timeframe." AND a.totrashoutbox=1 AND a.fromid=".(int)$myself." AND a.toid<>a.fromid AND ((systemmessage IS NULL) OR (systemmessage='')))
ORDER BY IF(totrashdate,totrashdate,totrashdateoutbox) DESC LIMIT ".(int)$limitstart.", ".(int)$limit;[/code:1]

It's little bit more complex. For 1.2 I have already added "fromid" but the above code needs some optimizations though

Slabbi...

I have an email thread going between the consulting company, Beat and myself (we're examining all queries). If you'd like to jump on there, we may be able to make some significant performanced improvements to the code pretty quickly, which will definitely benefit the community.

Please contact Beat or me via PM and we can add you to the email list, then remove you when uddeim workis complete.

Thanks!