Databases
StackOverflow2013
Postsdbo
POsplitting one query into four to avoid massive joins?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POsplitting one query into four to avoid massive joins?
    primarykey
    data
    text
    <p>So i have a query that looks like this:</p> <pre><code>SELECT col1, col2, col3 ... FROM action_6_members m LEFT JOIN action_6_5pts f ON f.member_id = m.id LEFT JOIN action_6_10pts t ON t.member_id = m.id LEFT JOIN action_6_weekly w ON w.member_id = m.id WHERE `draw_id` = '1' ORDER BY m.id DESC LIMIT 0, 20; </code></pre> <p>now this is doing a massive join (3.5 million * 40 thousand * 20 thousand) </p> <p>so my idea was to:</p> <p>do <code>SELECT * FROM action_6_members WHERE</code>draw_id<code>= '1' ORDER BY id DESC LIMIT 0, 20;</code></p> <p>then loop over that using php to build <code>$in = "IN(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)"</code>;</p> <p>then run<br> <code>select * from action_6_5pts where member_id in $in</code><br> <code>select * from action_6_10pts where member_id in $in</code><br> <code>select * from action_6_weekly where member_id in $in</code> </p> <p>then smush them all together using php,</p> <p>this means that, although i am using four different queries i am only selecting 20 rows from each, instead of doing the join on all.</p> <p>Will i notice a significant performance bonus?</p> <p><hr /> <strong>Update</strong><br /> So, the general consensus is, 'DONT DO THAT!'</p> <p>here is the general overview of the app</p> <p>it receives a code,</p> <p>the code is either a 5pt, 10pt, or a weekly code,</p> <p>all three code types are in seperate tables. the three tables have code, and member_id</p> <p>the member_id links to the id in the action_6_members table.</p> <p>when a code is claimed the data is filled in in the action_6_members table.</p> <p>the id of that member is then filled in in the table for the code that was claimed.</p> <p>the above query selects the first twenty members.</p> <p>So my question is then.</p> <p>What can I do to improve this?</p> <p>as currently everything is timing out before the queries complete.</p> <p><strong>action_6_members</strong></p> <pre><code>CREATE TABLE `action_6_members` ( `id` int(11) NOT NULL auto_increment, `draw_id` int(11) NOT NULL, `mobile` varchar(255) NOT NULL, `fly_buys` varchar(255) NOT NULL, `signup_date` datetime NOT NULL, `club` int(11) NOT NULL default '0' COMMENT '1 = yes, 2 = no', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=1337 DEFAULT CHARSET=latin1 </code></pre> <p><strong>action_6_ 5 and 10pts</strong></p> <pre><code>CREATE TABLE `action_6_5pts` ( `code` varchar(255) NOT NULL, `member_id` int(11) NOT NULL, PRIMARY KEY (`code`), KEY `member_id` (`member_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 </code></pre> <p><strong>action_6_weekly</strong></p> <pre><code>CREATE TABLE `action_6_weekly` ( `id` int(11) NOT NULL auto_increment, `code` varchar(255) NOT NULL, `member_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), KEY `member_id` (`member_id`) ) ENGINE=MyISAM AUTO_INCREMENT=3250001 DEFAULT CHARSET=latin1 </code></pre> <p><hr /> <strong>Update 2: explain the query</strong><br /></p> <pre> id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE m ALL \N \N \N \N 1390 Using temporary; Using filesort 1 SIMPLE f ALL member_id \N \N \N 36000 1 SIMPLE t ALL member_id \N \N \N 18000 Using where 1 SIMPLE w ref member_id member_id 4 m.id 525820 Using where </pre> <p>Just had this through: Latest load data from DB 7.26, 4.60, 2.45 </p> <p>1.0 is the normal max load...Anything above means it's had to 'burst' and call on additional processes to handle. i.e. 7.26 means load is 7 x the max of the blade server and has had to call on others to help out</p> <p>so currently this query is more than a monster, its eating monsters as snacks...</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USHailwood
    1. USHailwood
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POsplitting one query into four to avoid massive joins?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POsplitting one query into four to avoid massive joins?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POsplitting one query into four to avoid massive joins?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI think this will cause a performance hit, but I don't know for sure.
      singulars
      1. POsplitting one query into four to avoid massive joins?
      1. USjtbandes
    2. COThe correct answer to almost every performance question is "Try it and see."
      singulars
      1. POsplitting one query into four to avoid massive joins?
      1. USjergason
    3. COwell, its not an easy change to implement... so it is often better for me to ask on here.
      singulars
      1. POsplitting one query into four to avoid massive joins?
      1. USHailwood
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload