Geek Answers Handbook

Can I expect performance gains from removing this JOIN?

I have a table "items" with 1 million rows and a table "users" with 20,000 rows. When I select "items" from the table, I make a connection in the "users" table (items.user_id = user.id) so that I can grab the "username" from the users table.

I am considering adding a username column to the item table and removing the join. Can I expect a decent performance boost from this? This is already pretty fast, but it would be nice to reduce the load (which is pretty high).

The downside is that if the user changes his username, the elements will still reflect their previous username, but I'm fine if I can expect a decent performance increase.

I ask for stackoverflow because the tests don't tell me too much. Both requests end very quickly. Despite everything, I wonder if the connection will be removed to ease the load on the database to some extent.

Connection request example:
SELECT Item. id, Item. submitter_id, Item. source_image, Item. cached_image, Item. source_title, Item. source_url, Item. width, Item. height, Item. status, Item. popular, Item. made_popular, Item. fave_count, Item. tags, Item. user_art, Item. nudity, Item. created, Item. modified, Item. removed, Item. nofront, Item. test, Item. recs, Item. recs_data, User. id, User. username, User. password, User. email, User.fullname, User. profileurl, User. homepage, User. bio, User. location, User. avatar, User. ff_user, User. ff_key, User. ff_last_faveid, User. twitter_user, User. twitter_pass, User. emailalerts, User. showunsafe, User. view, User. fb_uid, User. fb_session, User. fb_avatar, User. twitter_uid, User. twitter_data, User. twitter_autopost, User. uri, User. created, User. modifiedFROM itemsAS ItemLEFT JOIN usersAS UserON ( Item. submitter_id= User. id) WHERE Item. nofront! = 1 ANDItem. removed! = 1 AND Item. made_popularis not NULL AND nudity! = 1 ORDER BY Item. made_popularDESC LIMIT 1040, 290;

:
SELECT Item. id, Item. submitter_id, Item. source_image, Item. cached_image, Item. source_title, Item. source_url, Item. width, Item. height, Item. status, Item. popular, Item. made_popular, Item. fave_count, Item. tags, Item. user_art, Item. nudity, Item. created, Item. modified, Item. removed, Item. nofront, Item. test, Item. recs, Item. recs_data FROM items AS Item WHERE Item. nofront!= 1 AND Item. removed!= 1 AND Item. made_popular NULL != 1 ORDER BY Item. made_popular DESC LIMIT 1040, 290;

+5
5

, , , . /, , .

- ( . , ). .

set-and-forget, . , .

. insert/update users, , , (, , ).

, MySQL , - DB2. - , , . , . , .

, - , , . , , , . ( ) , , bean , " ".

:

  • nudity. , : -)
  • . - , User, . Item - , .
  • , , WHERE - ( ). , , , , WHERE, .
  • "" (, RemovedItems), Items . , , romoved-, ( ). , . - ( , ).
+5

, . , , . ?

+1

, items.user_id user.id, . .

0

JOINS , SELECT. , JOIN .

0

"items" 1 "users" 20 000 .

, JOIN denormalize, 1M/20k = 50 User , . , .

.

JOIN ( ) (, User.profileurl, User.homepage ..), , , ? , User 50 . , , SELECT JOIN ( User, Item?)

stackoverflow, . . , , , - .

, , , , ( ), .

: ? , , ( OFFSET ... LIMIT ...), 50x User ( LIMIT .) 50x , SELECT ing User.id User.username (20k , -/map), SELECT Item rows (1M ) , Item.user_id -/.

Of course, always use EXPLAINto make sure that the correct indexes exist and are used when the index is used, and run ANALYZE TABLEafter any of your tables grow from a few hundred rows to thousands or millions.

0
source

More articles:


All Articles