vBulletin - Explain SQL Queries (130)

Duplicate Queries: (Exact textual duplicates, also included in the full listing of queries below)

Times run: 2

SELECT * FROM routenew
WHERE `routeid` IN (321)

Times run: 2

SELECT cl.parent AS nodeid, count(a.nodeid) AS count
FROM closure AS cl
INNER JOIN attach AS a ON (a.nodeid = cl.child)
WHERE cl.parent in (153418)
GROUP BY cl.parent
/**getDescendantAttachCount**/

Times run: 2

SELECT cl.parent AS nodeid, count(child.nodeid) AS count
FROM closure AS cl
INNER JOIN node AS child USE INDEX (nodeid, node_ctypid_userid_dispo_idx) ON (child.nodeid = cl.child)
WHERE cl.parent in (153418) AND child.contenttypeid = 16
GROUP BY cl.parent
/**getDescendantPhotoCount**/

SQL Query #2

SELECT * FROM datastore
WHERE `title` IN ('options','bitfields','attachmentcache','usergroupcache','stylecache','languagecache','products','cron','profilefield','loadcache','miscoptions','noticecache','hooks','publicoptions','vBUgChannelAccess','vBUgChannelPermissionsFrom','vBChannelTypes')

SQL Query #6

SELECT * FROM routenew
WHERE `prefix` IN ('forum/veröffentlichung-ausschreibung-interview/ausschreibungen-und-wettbewerbe','forum/veröffentlichung-ausschreibung-interview','forum')

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

routenew

range

prefix

prefix

202

4

Using index condition

Time Before: 1591519112.3332

Time After: 1591519112.3344

Time Taken: 0.0011630058288574

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #7

SELECT * FROM cache
WHERE `cacheid` IN ('vB_UserPerms0')

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

cache

const

PRIMARY

PRIMARY

66

const

1

Time Before: 1591519112.3351

Time After: 1591519112.3356

Time Taken: 0.00047707557678223

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #8

SELECT * FROM cache
WHERE `cacheid` IN ('vb_types.types')

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

cache

const

PRIMARY

PRIMARY

66

const

1

Time Before: 1591519112.3365

Time After: 1591519112.337

Time Taken: 0.00049304962158203

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #9

SELECT * FROM channel
WHERE `guid` IN ('vbulletin-4ecbdf567f2773.55528984','vbulletin-4ecbdf567f3341.44450667','vbulletin-4ecbdf567f3341.44451100','vbulletin-4ecbdf567f2c35.70389590','vbulletin-4ecbdf567f3a38.99555305','vbulletin-4ecbdf567f3a38.99555306','vbulletin-4ecbdf567f3a38.99555307','vbulletin-c-cmshome5229fa38b251e2.92227401','vbulletin-4ecbdf567f3da8.31769341','vbulletin-4ecbdf567f36c3.90966558','vbulletin-4ecbdf567f3a38.99555303','vbulletin-4ecbdf567f3a38.99555304','vbulletin-4ecbdf567f3a38.99555308')

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

channel

ALL

guid

73

Using where

Time Before: 1591519112.3394

Time After: 1591519112.3399

Time Taken: 0.00052499771118164

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #10

SELECT * FROM cache
WHERE `cacheid` IN ('node_120_lvl1data')

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

cache

const

PRIMARY

PRIMARY

66

const

1

Time Before: 1591519112.3402

Time After: 1591519112.3411

Time Taken: 0.00089001655578613

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #11

SELECT * FROM cache
WHERE `cacheid` IN ('channelperms_1')

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

cache

const

PRIMARY

PRIMARY

66

const

1

Time Before: 1591519112.3415

Time After: 1591519112.3422

Time Taken: 0.00067615509033203

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #12

SELECT * FROM cache
WHERE `cacheid` IN ('node_120_lvl3data')

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

cache

const

PRIMARY

PRIMARY

66

const

1

Time Before: 1591519112.3428

Time After: 1591519112.3436

Time Taken: 0.00078701972961426

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #13

SELECT * FROM cache
WHERE `cacheid` IN ('node_1_lvl1data')

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

cache

const

PRIMARY

PRIMARY

66

const

1

Time Before: 1591519112.3452

Time After: 1591519112.3457

Time Taken: 0.00050997734069824

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #14

SELECT * FROM node
WHERE `nodeid` = 1
LIMIT 1

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

node

const

PRIMARY,nodeid

PRIMARY

4

const

1

Time Before: 1591519112.3459

Time After: 1591519112.3463

Time Taken: 0.00040602684020996

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #15

SELECT * FROM cache
WHERE `cacheid` IN ('vB_NamedRoutes')

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

cache

const

PRIMARY

PRIMARY

66

const

1

Time Before: 1591519112.3467

Time After: 1591519112.3475

Time Taken: 0.00078606605529785

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #16

SELECT * FROM routenew
WHERE `routeid` = 233
LIMIT 1

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

routenew

const

PRIMARY

PRIMARY

4

const

1

Time Before: 1591519112.3481

Time After: 1591519112.3486

Time Taken: 0.00046396255493164

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #17

SELECT * FROM closure
WHERE `child` IN (120)
ORDER BY `child` ASC, `depth` ASC

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

closure

ref

child

child

4

const

4

Using where

Time Before: 1591519112.3494

Time After: 1591519112.3503

Time Taken: 0.00096511840820312

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #18

SELECT * FROM cache
WHERE `cacheid` IN ('node_2_lvl1data','node_118_lvl1data')

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

cache

range

PRIMARY

PRIMARY

66

2

Using where

Time Before: 1591519112.3506

Time After: 1591519112.3515

Time Taken: 0.00094389915466309

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #19

SELECT * FROM node
WHERE `nodeid` IN (2)

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

node

const

PRIMARY,nodeid

PRIMARY

4

const

1

Time Before: 1591519112.3518

Time After: 1591519112.3528

Time Taken: 0.0010149478912354

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #20

SELECT * FROM routenew
WHERE `routeid` IN (48,230,233)

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

routenew

range

PRIMARY

PRIMARY

4

3

Using where

Time Before: 1591519112.353

Time After: 1591519112.3535

Time Taken: 0.00047898292541504

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #21

SELECT * FROM cache
WHERE `cacheid` IN ('node_2_lvl3data')

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

Impossible WHERE noticed after reading const tables

Time Before: 1591519112.3538

Time After: 1591519112.3545

Time Taken: 0.00068807601928711

Memory Before: 2097152

Memory After: 2097152

Memory Used: 0

SQL Query #22

SELECT node.*,channel.nodeid AS channel_nodeid,channel.styleid,channel.daysprune,channel.newcontentemail,channel.defaultsortfield,channel.defaultsortorder,channel.imageprefix,channel.guid,channel.options,channel.filedataid,channel.category,channel.product, icon.iconpath, ch.routeid AS channelroute, ch.title AS channeltitle, ch.nodeid AS channelid,
starter.routeid AS starterroute, starter.title AS startertitle, starter.authorname as starterauthorname, starter.prefixid as starterprefixid,
starter.userid as starteruserid, starter.lastcontentid as starterlastcontentid, starter.totalcount+1 as startertotalcount, starter.urlident AS starterurlident,
deleteuser.username AS deleteusername, lastauthor.username AS lastauthorname, editlog.reason AS edit_reason, editlog.userid AS edit_userid, editlog.username AS edit_username,
editlog.dateline AS edit_dateline, editlog.hashistory, starter.nodeoptions as starternodeoptions, ch.nodeoptions as channelnodeoptions
FROM node AS node
LEFT JOIN channel AS channel
ON channel.nodeid = node.nodeid
LEFT JOIN editlog AS editlog ON (editlog.nodeid = node.nodeid)
LEFT JOIN node AS starter ON (starter.nodeid = node.starter)
LEFT JOIN node AS ch ON (ch.nodeid = starter.parentid)
LEFT JOIN user AS deleteuser ON (node.deleteuserid > 0 AND node.deleteuserid = deleteuser.userid)
LEFT JOIN user AS lastauthor ON (node.lastauthorid = lastauthor.userid)
LEFT JOIN icon AS icon ON (node.iconid = icon.iconid)
WHERE node.nodeid IN (2)
/** getFullContent**/

SQL Query #34

SELECT cl.parent AS nodeid, count(child.nodeid) AS count
FROM closure AS cl
INNER JOIN node AS child USE INDEX (nodeid, node_ctypid_userid_dispo_idx) ON (child.nodeid = cl.child)
WHERE cl.parent in (153418) AND child.contenttypeid = 16
GROUP BY cl.parent
/**getDescendantPhotoCount**/

SQL Query #38

SELECT `varname`,`languageid`,`text` FROM phrase
WHERE `varname` IN ('infraction_for_x_y','infraction_for_x_y_in_topic_z','warning_for_x_y','warning_for_x_y_in_topic_z','infraction_topic_post','infraction_topic_profile','infraction_received_ginfraction','warning_received_ginfraction','infraction_received_post','infraction_received_profile','warning_received_post','warning_received_profile','x_days','x_weeks','x_months','x_years','permanent') AND `languageid` IN (2,0,-1)

SQL Query #50

SELECT `varname`,`languageid`,`text` FROM phrase
WHERE `varname` IN ('widget_subchannelsdisplay_widgettitle','system_widgetcat','widget_channeldisplay_widgettitle','widget_announcement_widgettitle','display_widgetcat','widget_pagetitle_widgettitle','generic_widgetcat') AND `languageid` IN (2,0,-1)

SQL Query #65

SELECT * FROM searchlog
WHERE `searchhash` IN ('faced353ff550967416b695e4da61574') AND `userid` = 0 AND `dateline` >= 1591519052 AND `sortby` = 'lastcontent' AND `sortorder` = 'desc'
ORDER BY `dateline` ASC

SQL Query #79

SELECT cl.parent AS nodeid, count(a.nodeid) AS count
FROM closure AS cl
INNER JOIN attach AS a ON (a.nodeid = cl.child)
WHERE cl.parent in (19135,19136,19188,5801,1189)
GROUP BY cl.parent
/**getDescendantAttachCount**/

SQL Query #80

SELECT cl.parent AS nodeid, count(child.nodeid) AS count
FROM closure AS cl
INNER JOIN node AS child USE INDEX (nodeid, node_ctypid_userid_dispo_idx) ON (child.nodeid = cl.child)
WHERE cl.parent in (19135,19136,19188,5801,1189) AND child.contenttypeid = 16
GROUP BY cl.parent
/**getDescendantPhotoCount**/

SQL Query #89

SELECT * FROM searchlog
WHERE `searchhash` IN ('56c118687bca160e16f14c854b96da6a') AND `userid` = 0 AND `dateline` >= 1591519052 AND `sortby` = 'lastcontent' AND `sortorder` = 'desc'
ORDER BY `dateline` ASC

SQL Query #94

SELECT * FROM cache
WHERE `cacheid` IN ('node_170208_lvl1data','node_154180_lvl1data','node_147627_lvl1data','node_167207_lvl1data','node_166644_lvl1data','node_165626_lvl1data','node_164437_lvl1data','node_164069_lvl1data','node_158219_lvl1data','node_157979_lvl1data','node_154915_lvl1data','node_154908_lvl1data','node_154179_lvl1data','node_157636_lvl1data','node_143693_lvl1data')

SQL Query #102

SELECT * FROM cache
WHERE `cacheid` IN ('vBAtchmnts_170208','vBAtchmnts_154180','vBAtchmnts_147627','vBAtchmnts_167207','vBAtchmnts_166644','vBAtchmnts_165626','vBAtchmnts_164437','vBAtchmnts_164069','vBAtchmnts_158219','vBAtchmnts_157979','vBAtchmnts_154915','vBAtchmnts_154908','vBAtchmnts_154179','vBAtchmnts_157636','vBAtchmnts_143693')

SQL Query #103

SELECT cl.parent AS nodeid, count(a.nodeid) AS count
FROM closure AS cl
INNER JOIN attach AS a ON (a.nodeid = cl.child)
WHERE cl.parent in (170208,154180,147627,167207,166644,165626,164437,164069,158219,157979,154915,154908,154179,157636,143693)
GROUP BY cl.parent
/**getDescendantAttachCount**/

SQL Query #104

SELECT cl.parent AS nodeid, count(child.nodeid) AS count
FROM closure AS cl
INNER JOIN node AS child USE INDEX (nodeid, node_ctypid_userid_dispo_idx) ON (child.nodeid = cl.child)
WHERE cl.parent in (170208,154180,147627,167207,166644,165626,164437,164069,158219,157979,154915,154908,154179,157636,143693) AND child.contenttypeid = 16
GROUP BY cl.parent
/**getDescendantPhotoCount**/

SQL Query #106

SELECT * FROM cache
WHERE `cacheid` IN ('node_170208_lvl3data','node_154180_lvl3data','node_147627_lvl3data','node_167207_lvl3data','node_166644_lvl3data','node_165626_lvl3data','node_164437_lvl3data','node_164069_lvl3data','node_158219_lvl3data','node_157979_lvl3data','node_154915_lvl3data','node_154908_lvl3data','node_154179_lvl3data','node_143693_lvl3data')

SQL Query #114

SELECT cl.parent AS nodeid, count(child.nodeid) AS count
FROM closure AS cl
INNER JOIN node AS child USE INDEX (nodeid, node_ctypid_userid_dispo_idx) ON (child.nodeid = cl.child)
WHERE cl.parent in (153418) AND child.contenttypeid = 16
GROUP BY cl.parent
/**getDescendantPhotoCount**/

SQL Query #126

SELECT `varname`,`languageid`,`text` FROM phrase
WHERE `varname` IN ('login_or_sign_up','you_can_also_login_as','logo','powered_by_vbulletin_title','search','filter_search','sections','navbar_home','navbar_articles','navbar_absprachenmediation','navbar_themenfilter','navbar_aktive_themen','navbar_regeln','navbar_faq','home','search_in_titles_only','search_in_x_only','advanced_search','notice_1_html','announcement','see_more','see_less','collapse','expand','page_title','subforums','forums','topics','posts','last_post','no_forums_found','mark_channels_read','topic','latest_activity','my_subscriptions','photos','no_topics_found','mark_this_channel_read','filter','time','all_time','today','last_week','last_month','show','all','discussions_only','photos_only','videos_only','links_only','polls_only','sort_by','date_last_update','date_started_post','replies','title','members','likes','order','descending','ascending','prefix','any_topic_meta','no_prefix_meta','any_prefix_meta','prefixset_Information_title','prefixset_Status_title','filtered_by','clear_all','conversation_new_posts','page','of_pagination','previous_page','next_page','statistics','sticky_node','by_x','started_by_link_x_y','on_date_x','x_responses_lower','views_lower','like_lower','go_to_last_post','likes_lower','closed','this_topic_is_closed','1_response_lower','redirect','previous','next','navbar_contact_us','navbar_impressum','go_to_top','powered_by_vbulletin','footer_current_time','working_ellipsis','yes','no','ok','cancel') AND `languageid` IN (2,0,-1)

SQL Query #130

SELECT `varname`,`languageid`,`text` FROM phrase
WHERE `varname` IN ('debug_information','page_id','page_parent_id','page_template_id','route_id','page_type','screen_layout_id','screen_layout_template','channel_id','node_id','database_asserts','ajax_requests','facebook_active','not_enabled_or_no_appid_secret_set','style_id','templates_renders','parent','css_templates','rollup','js_files','included_files','autoloaded_classes','not_loaded','current_time','page_time') AND `languageid` IN (2,0,-1)