Mysql – MyISAM Performance: Join Decomposition

myisamMySQLperformance

in High Performance MySQL on page 159 they talk about breaking up complex queries into simple ones:

Converting

SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);

And sort of doing the actual join yourself in your application.

My Question is whether this is stil such a good idea when the final query has a where-clause with a few thousand IDs it needs to match (the actual table itself has about 500k entries).

What I mean is, will there be a big penalty for having a query like

SELECT * FROM post WHERE post.id in (123,456,567, ... <a few thousand IDs here> ... ,9098,8904);

instead of the join-statement above? Would it help to move this logic to Stored Procedures inside the Database (while considering how poorly stored procedures are implemented in MySQL)?

Best Answer

I have done this in a few places. Doing multiple simple queries and building an ID list in the application logic, even with the ID list containing 10,000+ ID's made significant performance increases. The table I was querying had around 5 million records and doing a JOIN was painfully slow. After switching to using IN with an ID list it took about 1% of the time the JOIN took.

Related Solutions

Mysql – execute a prepared statement inside a MySQL SELECT

From the question, it looks like what you have written quasi-resembles what Oracle calls an anonymous code block. MySQL does not facilitate such a mechanism.

With regard to what you want to accomplish, you do not need a stored procedure.

Try assembling the SQL statement like this:

@stmt = 'SELECT sid, ifnull(pricelist,"BASE"), count(*) AS recs FROM buyerList AS b ';
@stmt = CONCAT(@stmt,'LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass ');
@stmt = CONCAT(@stmt,'LEFT JOIN pricelists p ON ');
SELECT GROUP_CONCAT(CONCAT('(p.sid = a.sid AND p.preisliste = ',sid,' AND p.ean = a.ean AND p.iln = ',pricelist',)') SEPARATOR ' OR ')
INTO @LeftJoinClause FROM preislisten;
@stmt = CONCAT(@stmt,@LeftJoinClause,' WHERE b.bid = ?');
SELECT @stmt\G

This will print out the desired query

If it is the desired query, execute it

PREPARE sql FROM @stmt;
EXECUTE sql using @param_iln;
DEALLOCATE PREPARE sql;

Give it a Try !!!

If you actually want the query to be small without hardcoding every value just write the code with a more straightforward LEFT JOIN setup

SELECT p.sid, ifnull(p.pricelist,"BASE"), count(*) AS recs FROM buyerList AS b
LEFT JOIN sellerList AS s ON s.sid = b.sid AND s.pass = b.pass
LEFT JOIN pricelists p ON
    (p.sid = a.sid AND p.preisliste = a.sid AND p.ean = a.ean)
WHERE b.bid = ?
GROUP BY p.sid,p.pricelist;

I also just noticed you have a.sid and a.ean, what table has an alias of a ?

Mysql – Index optimization

I think that the best for your tag and posttag tables is:

ok for the tagid as a primary key
add an index on your tag field (this cover your second query)
add two index on posttag table like INDEX(postid,tagid) and INDEX(tagid,postid) (this cover your first and second query)

So:

CREATE TABLE `posts` (
  `postid` mediumint(9) NOT NULL AUTO_INCREMENT,
  `post` varchar(255) NOT NULL,
  PRIMARY KEY (`postid`)
);

CREATE TABLE `posttags` (
  `postid` mediumint(9) NOT NULL,
  `tagid` mediumint(9) NOT NULL,
  PRIMARY KEY (`postid`,`tagid`),
  UNIQUE KEY `tagid_postid_UQ` (`tagid`,`postid`),
  FOREIGN KEY (postid)
    REFERENCES posts (postid),
  FOREIGN KEY (tagid)
    REFERENCES tags (tagid)
);

CREATE TABLE `tags` (
  `tagid` mediumint(9) NOT NULL AUTO_INCREMENT,
  `tag` char(30) NOT NULL,
  PRIMARY KEY (`tagid`),
  UNIQUE KEY `tag_UQ` (`tag`)
);

And for your query:

mysql> explain select p.* from tags as t    inner join posttags as pt on t.tagid = pt.tagid   inner join posts as p on p.postid = pt.postid  where t.tag =  ?;
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref            | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref    | PRIMARY,tag   | tag     | 32      | const          |    1 | Using where; Using index |
|  1 | SIMPLE      | pt    | ref    | postid,tagid  | tagid   | 3       | test.t.tagid   |    1 | Using index              |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY       | PRIMARY | 4       | test.pt.postid |    1 |                          |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+


explain select p.* from posts as p    inner join posttags as pt on pt.postid = p.postid   inner join tags as t on t.tagid = pt.tagid  where p.postid = ?;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | p     | const  | PRIMARY       | PRIMARY | 4       | const         |    1 |             |
|  1 | SIMPLE      | pt    | ref    | postid,tagid  | postid  | 4       | const         |    2 | Using index |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY       | PRIMARY | 3       | test.pt.tagid |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+

Best Answer

Related Solutions

Mysql – execute a prepared statement inside a MySQL SELECT

Mysql – Index optimization

Related Question