JOIN on 3 tables being slow

everyone.
Can't figure out what indices are missing or how to rephrase my query.
I created about a milllion of test entries in each table to see performance. Query takes about 1.5 seconds which is infinitely more than what I need it to take (should be pretty much immediate).

Thanks for any help.

Tables:

``
CREATE TABLE chars (
pid int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
x int(11) NOT NULL DEFAULT '0',
y int(11) NOT NULL DEFAULT '0',
z int(11) NOT NULL DEFAULT '0',
zone varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
zonefrom varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
password varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
session varchar(80) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
surname varchar(80) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
bind_zone varchar(80) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
angle int(11) NOT NULL DEFAULT '0',
bind_x int(11) NOT NULL DEFAULT '0',
bind_y int(11) NOT NULL DEFAULT '0',
bind_z int(11) NOT NULL DEFAULT '0',
bind_angle int(11) NOT NULL DEFAULT '0',
xp bigint(20) unsigned NOT NULL DEFAULT '0',
hp int(11) NOT NULL DEFAULT '0',
mana int(10) unsigned NOT NULL DEFAULT '0',
endurance int(10) unsigned NOT NULL DEFAULT '0',
copper int(10) unsigned NOT NULL DEFAULT '0',
silver int(10) unsigned NOT NULL DEFAULT '0',
gold int(10) unsigned NOT NULL DEFAULT '0',
platinum int(10) unsigned NOT NULL DEFAULT '0',
zoning_ts int(10) unsigned NOT NULL DEFAULT '0',
clearance int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (pid),
UNIQUE KEY name (name),
KEY zone (zone)
) ENGINE=MyISAM AUTO_INCREMENT=1000004 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE inv (
pid int(10) unsigned NOT NULL DEFAULT '0',
slot int(10) unsigned NOT NULL DEFAULT '0',
institem` bigint(20) unsigned NOT NULL DEFAULT '0',

Use plain JOIN, not LEFT

SELECT SQL_NO_CACHE chars.name 
FROM chars
    JOIN inv ON (chars.pid = inv.pid)
    JOIN institems ON (inv.institem = institems.id)
    WHERE
      inv.institem = 10001013730 ||
      institems.container_slot_1 = 10001013730 ||
      institems.container_slot_2 = 10001013730 ||
      institems.container_slot_3 = 10001013730 ||
      institems.container_slot_4 = 10001013730 ||
      institems.container_slot_5 = 10001013730 ||
      institems.container_slot_6 = 10001013730 ||
      institems.container_slot_7 = 10001013730 ||
      institems.container_slot_8 = 10001013730 ||
      institems.container_slot_9 = 10001013730 ||
      institems.container_slot_10 = 10001013730;

It is obvious because with left joins you'll get alot of rows where inv and institems will be NULL and WHERE clause have no sense but make heavy load.

StackExchange Database Administrators Q#75222, answer score: 3