StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL break up left table in LEFT JOIN query for performance enhancement
text
Body
copied!<p>I have the following MySQL query:</p> <pre><code>SELECT pool.username FROM pool LEFT JOIN sent ON pool.username = sent.username AND sent.campid = 'YA1LGfh9' WHERE sent.username IS NULL AND pool.gender = 'f' AND (`location` = 'united states' OR `location` = 'us' OR `location` = 'usa'); </code></pre> <p>The problem is that the pool table contains millions of rows and this query takes over 12 minutes to complete. I realize that in this query, the entire left table (pool) is being scanned. The pool table has an auto incremented id row.</p> <p>I would like to split this query into multiple queries so that rather than scanning the entire pool table I scan 1000 rows at a time and in the next query I would pick up where I left off (1000-2000,2000-3000) and so on using the id column to keep track.</p> <p>How can I specify this in my query? Please show examples if you know the answer. Thank you.</p> <p>here are my indexes if it helps:</p> <pre><code>mysql> show index from main.pool; | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | pool | 0 | PRIMARY | 1 | id | A | 9275039 | NULL | NULL | | BTREE | | | pool | 1 | username | 1 | username | A | 9275039 | NULL | NULL | | BTREE | | | pool | 1 | source | 1 | source | A | 1 | NULL | NULL | | BTREE | | | pool | 1 | location | 1 | location | A | 38168 | NULL | NULL | | BTREE | | | pool | 1 | pdex | 1 | gender | A | 2 | NULL | NULL | | BTREE | | | pool | 1 | pdex | 2 | username | A | 9275039 | NULL | NULL | | BTREE | | | pool | 1 | pdex | 3 | id | A | 9275039 | NULL | NULL | | BTREE | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 8 rows in set (0.00 sec) mysql> show index from main.sent; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | sent | 0 | PRIMARY | 1 | primary_key | A | 351 | NULL | NULL | | BTREE | | | sent | 1 | username | 1 | username | A | 175 | NULL | NULL | | BTREE | | | sent | 1 | sdex | 1 | campid | A | 7 | NULL | NULL | | BTREE | | | sent | 1 | sdex | 2 | username | A | 351 | NULL | NULL | | BTREE | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ </code></pre> <p>and here is the explain for my query:</p> <pre><code>----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+-------+---------+--------------------------------------+ | 1 | SIMPLE | pool | ref | location,pdex | pdex | 5 | const | 6084332 | Using where | | 1 | SIMPLE | sent | index | sdex | sdex | 309 | NULL | 351 | Using where; Using index; Not exists | +----+-------------+-------+-------+---------------+------+---------+-------+---------+--------------------------------------+ </code></pre> <p>here is the structure of the pool table:</p> <pre><code>| pool | CREATE TABLE `pool` ( `id` int(20) NOT NULL AUTO_INCREMENT, `username` varchar(50) CHARACTER SET utf8 NOT NULL, `source` varchar(10) CHARACTER SET utf8 NOT NULL, `gender` varchar(1) CHARACTER SET utf8 NOT NULL, `location` varchar(50) CHARACTER SET utf8 NOT NULL, PRIMARY KEY (`id`), KEY `username` (`username`), KEY `source` (`source`), KEY `location` (`location`), KEY `pdex` (`gender`,`username`,`id`) ) ENGINE=MyISAM AUTO_INCREMENT=9327026 DEFAULT CHARSET=latin1 | </code></pre> <p>here is the structure of the sent table:</p> <pre><code>| sent | CREATE TABLE `sent` ( `primary_key` int(50) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `from` varchar(50) NOT NULL, `campid` varchar(255) NOT NULL, `timestamp` int(20) NOT NULL, PRIMARY KEY (`primary_key`), KEY `username` (`username`), KEY `sdex` (`campid`,`username`) ) ENGINE=MyISAM AUTO_INCREMENT=352 DEFAULT CHARSET=latin1 | </code></pre> <p>This produces a syntax error but this WHERE clause in the beginning is what im after:</p> <pre><code>SELECT pool.username FROM pool WHERE id < 1000 LEFT JOIN sent ON pool.username = sent.username AND sent.campid = 'YA1LGfh9' WHERE sent.username IS NULL AND pool.gender = 'f' AND (location = 'united states' OR location = 'us' OR location = 'usa'); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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