java - Can I make this request more efficient using index? -


i have bean/table "userinfo" columns id, username, , twitchchannel. userinfo twitchchannel column null. i'm going through every userinfo entity in table , search column twitchchannel, if column not null put twitchchannel in array.

this request looks like:

"select ui userinfo ui ui.iduserinfo=:id" 

it inefficient because i'm going through every single entity have null twitchchannel , i'm not interested in those.

this java commented every line it's easy understand don't know it.

    while (true) { // i'm going through table in infinite loop         int id = 0; //id incremented searches         userinfo ui;  // object hold result of query         {             ui = ups.getuserinfo(id); // execute query posted above             id++; //incrementing id next search             if (ui.gettwitch() != null) {  // if search didn't return null                 twitchchannels.add(ui.gettwitch());   // put twitch in array             }         } while (ui != null);     } 

so @ moment i'm going through every entity in table null twitch. understanding it's possible speed process indexes.

create index twitchchannel on userinfo (twitchchannel) 

so have table not null twitchchannel. how loop through table above ? work same way java persistence?

change query to:

select ui userinfo ui twitchchannel not null 

this benefit index on userinfo(twitchchannel) (assuming there few values filled in). @ least, reduces amount of data passed database application, if index not used.


Comments

Popular posts from this blog

twig - Using Twigbridge in a Laravel 5.1 Package -

jdbc - Not able to establish database connection in eclipse -

Kivy: Swiping (Carousel & ScreenManager) -