Global Login Search Speed

[lekky]

The searching for a global login account is very slow, 8 seconds (ty yslow) leaves me pulling my hair out.

Jitspoe, fancy optimising this search/table? Or care to post search sql/table here and we can have a go?

[XtremeBain]

I'd recommend against posting the SQL schema here, you're just opening yourself up further to injection attacks.  I'm sure the entire systems needs to have some profiling done to save on performance costs, with the activity level of paintball, I'm pretty sure the system comes under quite a bit of load.

[lekky]

Can't beat stored procedures

I've only noticed that search as I, and i'm sure others must use it quite regularly. Its something that can be fixed quite simply too i'm sure.

[jitspoe]

I'm really not sure why it's so slow.  It isn't always slow.   Here's the query:

SELECT `dplogin_accounts`.`name` as `name`, `dplogin_accounts`.`id` as `id` FROM `dplogin_accounts` LEFT JOIN `dplogin_names` ON `dplogin_accounts`.`id` = `dplogin_names`.`playerid` WHERE (`dplogin_accounts`.`flags` & $flags) = '$flags' AND `dplogin_names`.`name` = '$search' OR `dplogin_accounts`.`name` = '$searchexact' OR `dplogin_accounts`.`email` = '$searchexact' GROUP BY `dplogin_accounts`.`id` LIMIT $start, $displaylimit;

The group by probably kills it.  I could take that out and see if it speeds it up any.  Naturally name is indexed, and id is a primary key.

Oh, I just realized that I added the exact name search (in case somebody searches for a name with a clan tag or whatever like it may appear in-game).  I added an index for that, too.  We'll see if that helps.

[lekky]

Apparently MATCH is preferable over = (EDIT: on full-text indexes).

eg MATCH (`dplogin_names`) AGAINST ('$search')

I'll take a look around see if i can help.

Apparently indexes lose their speed advantage when using them in OR-situations. Would the following produce the same result?

(SELECT `acc`.`name`, `acc`.`id`
FROM `dplogin_accounts` acc
WHERE (`acc`.`flags` & $flags) = '$flags'
AND `acc`.`name` = '$searchexact'
OR `acc`.`email` = '$searchexact' )

UNION

(select `acc`.`name`, `acc`.`id`
FROM `dplogin_names` n
LEFT JOIN `dplogin_accounts` acc  ON `n`.`playerid` = `acc.`id`
WHERE  (`acc`.`flags` & $flags) = '$flags' AND `n`.`name` = '$search' )

[jitspoe]

That seems to be a lot faster.  I'm not sure if it's because of the union or because it doesn't have the grouping.  The grouping isn't really necessary at the moment since I don't have substring matches.  I disabled that a while back because I thought it might slow it down plus it makes it hard to find people with short names.

[lekky]

So much faster!! half a second where it took 8 seconds before!