Improving performance in this query

I have 3 tables with user logins:

sis_login => administrators tb_rb_estrutura => coordinators tb_usuario => clients

I created a VIEW to unite all these users by separating them by levels, as follows:

create view `login_names` as select `n1`.`cod_login` as `id`, '1' as `level`, `n1`.`nom_user` as `name` from `dados`.`sis_login` `n1`
union all
select `n2`.`id` as `id`, '2' as `level`, `n2`.`nom_funcionario` as `name` from `tb_rb_estrutura` `n2`
union all
select `n3`.`cod_usuario` as `id`, '3'开发者_如何转开发 as `level`, `n3`.`dsc_nome` as `name` from `tb_usuario` `n3`;

So, can occur up to three ids repeated for different users, which is why I separated by levels. This VIEW is just to return me user name, according to his id and level. considering it has about 500,000 registered users, this view takes about 1 second to load. too much time, but is becomes very small when I need to return the latest posts on the forum of my website.

The tables of the forums return the user id and level, then look for a name in this VIEW. I have registered 18 forums. When I run the query, it takes one second for each forum = 18 seconds. OMG. This page loads every time somebody enter my website.

This is my query:

select `x`.`forum_id`, `x`.`topic_id`, `l`.`nome`
from (
select `t`.`forum_id`,  `t`.`topic_id`, `t`.`data`, `t`.`user_id`, `t`.`user_level`
from `tb_forum_topics` `t`
union all
select `a`.`forum_id`, `a`.`topic_id`, `a`.`data`, `a`.`user_id`,  `a`.`user_level`
from `tb_forum_answers` `a` ) `x`
left outer join `login_names` `l`
on `l`.`id` = `x`.`user_id` and `l`.`level` = `x`.`user_level`
group by `x`.`forum_id` asc

USING EXPLAIN:

id  select_type table       type    possible_keys   key key_len ref rows    Extra
1   PRIMARY     <derived2>  ALL NULL        NULL    NULL    NULL    6   Using temporary; Using filesort
1   PRIMARY     <derived4>  ALL NULL        NULL    NULL    NULL    530415   
4   DERIVED     n1      ALL NULL        NULL    NULL    NULL    114  
5   UNION       n2      ALL NULL        NULL    NULL    NULL    2    
6   UNION       n3      ALL NULL        NULL    NULL    NULL    530299   

NULL UNION RESULT ALL NULL NULL NULL NULL NULL

2 DERIVED t ALL NULL NULL NULL NULL 3

3 UNION r ALL NULL NULL NULL NULL 3

NULL UNION RESULT ALL NULL NULL NULL NULL NULL

Somebody can help me or give a suggestion?

---

To do what you want:

Do a query where name = 'whatever'.

This will return to you just the row that you want. Returning all rows will get very slow very quickly as the number of users increases. And you're doing it 3 times.

Make sure that name is indexed to make it very quick.

In a function that calls this, cache any name you've already requested in a hash. If it is not set, do the query, put the result in the hash. If it is set, return the value.

More information about how this is being called would be very helpful.

I would actually recommend a different table structure:

Table 1: Users
UserId, Name

Table 2: Permissions
id, UserId, Level

Hope this helps.