Extremely Slow Query

[Ryokotsusai]

Hi, I am sorry if this is a dumb question, but

 

I have a php page that administrators for a mmorpg can use to make finding strange activities a little easier, but because I am not that great with MySQL, to get everything needed, the query became large and very slow, it still works just fine right now, it just takes forever

 

So i am wondering if there is a lighter, or easier way of running this query:

SELECT `c`.`name`, `a`.`type`, `b`.`name_japanese`, `a`.`amount`, `a`.`time`, `c`.`char_id`, `c`.`account_id`, `a`.`nameid`
FROM (`gravity`.`picklog` `a` INNER JOIN `gravity`.`item_db` `b` ON `a`.`nameid` = `b`.`id` ) 
INNER JOIN `gravity`.`char` `c` ON `c`.`char_id` = `a`.`char_id` 
WHERE `a`.`char_id` = 'Ryokotsusai' OR `c`.`name` LIKE CONVERT( _utf8 '%Ryokotsusai%' USING latin1 ) 
AND (`b`.`name_japanese` LIKE '%Apple%' OR `b`.`name_english` LIKE '%Apple%' OR `a`.`nameid` = 'Apple') 
UNION
SELECT `c`.`name`, `a`.`type`, `b`.`name_japanese`, `a`.`amount`, `a`.`time`, `c`.`char_id`, `c`.`account_id`, `a`.`nameid` 
FROM (`gravity`.`picklog` `a` INNER JOIN `gravity`.`item_db2` `b` ON `a`.`nameid` = `b`.`id` ) 
INNER JOIN `gravity`.`char` `c` ON `c`.`char_id` = `a`.`char_id` 
WHERE `a`.`char_id` = 'Ryokotsusai' OR `c`.`name` LIKE CONVERT( _utf8 '%Ryokotsusai%' USING latin1 ) 
AND (`b`.`name_japanese` LIKE '%Apple%' OR `b`.`name_english` LIKE '%Apple%' OR `a`.`nameid` = 'Apple') 
ORDER BY 5 DESC LIMIT 0,100

 

the reason I am specifying the db to read from (gravity.whatever) is not all people who use this script use the same db for their logs and their server info so the gravity.picklog could be log.picklog or whatever they set it to

 

 

i don't think it is needed, but the 'read this first' post said to include it, but here is the structure for the tables it uses

 

gravity.char:

CREATE TABLE `char` (
  `char_id` int(11) unsigned NOT NULL auto_increment,
  `account_id` int(11) unsigned NOT NULL default '0',
  `char_num` tinyint(1) NOT NULL default '0',
  `name` varchar(30) NOT NULL default '',
  `class` smallint(6) unsigned NOT NULL default '0',
  `base_level` smallint(6) unsigned NOT NULL default '1',
  `job_level` smallint(6) unsigned NOT NULL default '1',
  `base_exp` bigint(20) unsigned NOT NULL default '0',
  `job_exp` bigint(20) unsigned NOT NULL default '0',
  `zeny` int(11) unsigned NOT NULL default '0',
  `str` smallint(4) unsigned NOT NULL default '0',
  `agi` smallint(4) unsigned NOT NULL default '0',
  `vit` smallint(4) unsigned NOT NULL default '0',
  `int` smallint(4) unsigned NOT NULL default '0',
  `dex` smallint(4) unsigned NOT NULL default '0',
  `luk` smallint(4) unsigned NOT NULL default '0',
  `max_hp` mediumint( unsigned NOT NULL default '0',
  `hp` mediumint( unsigned NOT NULL default '0',
  `max_sp` mediumint(6) unsigned NOT NULL default '0',
  `sp` mediumint(6) unsigned NOT NULL default '0',
  `status_point` smallint(4) unsigned NOT NULL default '0',
  `skill_point` smallint(4) unsigned NOT NULL default '0',
  `option` int(11) NOT NULL default '0',
  `karma` tinyint(3) NOT NULL default '0',
  `manner` tinyint(3) NOT NULL default '0',
  `party_id` int(11) unsigned NOT NULL default '0',
  `guild_id` int(11) unsigned NOT NULL default '0',
  `pet_id` int(11) unsigned NOT NULL default '0',
  `homun_id` int(11) unsigned NOT NULL default '0',
  `hair` tinyint(4) unsigned NOT NULL default '0',
  `hair_color` smallint(5) unsigned NOT NULL default '0',
  `clothes_color` smallint(5) unsigned NOT NULL default '0',
  `weapon` smallint(6) unsigned NOT NULL default '1',
  `shield` smallint(6) unsigned NOT NULL default '0',
  `head_top` smallint(6) unsigned NOT NULL default '0',
  `head_mid` smallint(6) unsigned NOT NULL default '0',
  `head_bottom` smallint(6) unsigned NOT NULL default '0',
  `last_map` varchar(20) NOT NULL default 'prontera.gat',
  `last_x` smallint(4) unsigned NOT NULL default '53',
  `last_y` smallint(4) unsigned NOT NULL default '111',
  `save_map` varchar(20) NOT NULL default 'prontera.gat',
  `save_x` smallint(4) unsigned NOT NULL default '53',
  `save_y` smallint(4) unsigned NOT NULL default '111',
  `partner_id` int(11) unsigned NOT NULL default '0',
  `online` tinyint(2) NOT NULL default '0',
  `father` int(11) unsigned NOT NULL default '0',
  `mother` int(11) unsigned NOT NULL default '0',
  `child` int(11) unsigned NOT NULL default '0',
  `fame` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`char_id`),
  KEY `account_id` (`account_id`),
  KEY `party_id` (`party_id`),
  KEY `guild_id` (`guild_id`),
  KEY `name` (`name`),
  KEY `online` (`online`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=150077 ;

 

 

gravity.item_db & gravity.item_db2 are the same setup:

CREATE TABLE `item_db` (
  `id` smallint(5) unsigned NOT NULL default '0',
  `name_english` varchar(50) NOT NULL default '',
  `name_japanese` varchar(50) NOT NULL default '',
  `type` tinyint(2) unsigned NOT NULL default '0',
  `price_buy` mediumint(10) unsigned default NULL,
  `price_sell` mediumint(10) unsigned default NULL,
  `weight` smallint(5) unsigned NOT NULL default '0',
  `attack` smallint(3) unsigned default NULL,
  `defence` tinyint(3) unsigned default NULL,
  `range` tinyint(2) unsigned default NULL,
  `slots` tinyint(2) unsigned default NULL,
  `equip_jobs` int(12) unsigned default NULL,
  `equip_upper` tinyint( unsigned default NULL,
  `equip_genders` tinyint(2) unsigned default NULL,
  `equip_locations` smallint(4) unsigned default NULL,
  `weapon_level` tinyint(2) unsigned default NULL,
  `equip_level` tinyint(3) unsigned default NULL,
  `refineable` tinyint(1) unsigned default NULL,
  `view` smallint(3) unsigned default NULL,
  `script` text,
  `equip_script` text,
  `unequip_script` text,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

 

gravity.picklog

CREATE TABLE `picklog` (
  `id` int(11) NOT NULL auto_increment,
  `time` datetime NOT NULL default '0000-00-00 00:00:00',
  `char_id` int(11) NOT NULL default '0',
  `type` set('M','P','L','T','V','S','N','C','A') NOT NULL default 'P',
  `nameid` int(11) NOT NULL default '0',
  `amount` int(11) NOT NULL default '1',
  `refine` tinyint(3) unsigned NOT NULL default '0',
  `card0` int(11) NOT NULL default '0',
  `card1` int(11) NOT NULL default '0',
  `card2` int(11) NOT NULL default '0',
  `card3` int(11) NOT NULL default '0',
  `map` varchar(20) NOT NULL default 'prontera.gat',
  `card4` int(11) NOT NULL default '0',
  `card5` int(11) NOT NULL default '0',
  `card6` int(11) NOT NULL default '0',
  `card7` int(11) NOT NULL default '0',
  `card8` int(11) NOT NULL default '0',
  `card9` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `type` (`type`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=484728 ;

 

Also I am not allowed to alter anything DB side only read from it

 

 

 

[btherl]

Can you narrow the query down to a smaller query that still shows poor performance?  For example, try the first select from the union, along with the order by condition, and see if that is still slow.

 

At a glance, I suspect the problem is with your LIKE.  LIKE can be a very slow operation, especially when it's not anchored at the start (that is, '%Apple%' is slow, but 'Apple%' can be much faster).  The reason is that it requires examining every single row in the table.

 

So try removing some of those LIKE conditions and see if things speed up.  Once you've isolated what is causing the slowdown, it'll be much easier to suggest solutions.

[Ryokotsusai]

I ran just the first part, before the union, and it ran close to the same time, I then removed the:

AND (`b`.`name_japanese` LIKE '%Apple%' OR `b`.`name_english` LIKE '%Apple%' OR `a`.`nameid` = 'Apple') 

 

and that really didn't affect it much which surprised me as the item_db table has over 3,000 rows

 

then i tried without the CONVERT (just in case)

 

and the last thing i tried with the first half searching by item name instead of by character name:

 SELECT `c`.`name` , `a`.`type` , `b`.`name_japanese` , `a`.`amount` , `a`.`time` , `c`.`char_id` , `c`.`account_id` , `a`.`nameid`
FROM (
`gravity`.`picklog` `a`
INNER JOIN `gravity`.`item_db` `b` ON `a`.`nameid` = `b`.`id`
)
INNER JOIN `gravity`.`char` `c` ON `c`.`char_id` = `a`.`char_id`
WHERE (
`b`.`name_japanese` LIKE '%Apple%'
OR `b`.`name_english` LIKE '%Apple%'
OR `a`.`nameid` = 'Apple'
)
ORDER BY 5 DESC
LIMIT 0 , 100 

 

and that brought it down to 2-3 seconds

 

so i guess its the

`a`.`char_id` = 'Ryokotsusai' OR `c`.`name` LIKE CONVERT( _utf8 '%Ryokotsusai%' USING latin1 ) 

 

line thats killing the individual queries, but as a whole without that line:

SELECT `c`.`name`, `a`.`type`, `b`.`name_japanese`, `a`.`amount`, `a`.`time`, `a`.`char_id` 
FROM (`gravity`.`picklog` `a` INNER JOIN `gravity`.`item_db` `b` ON `a`.`nameid` = `b`.`id` ) 
INNER JOIN `gravity`.`char` `c` ON `c`.`char_id` = `a`.`char_id` 
WHERE (`b`.`name_japanese` LIKE '%apple%' OR `a`.`nameid` = 'apple') 
UNION 
SELECT `c`.`name`, `a`.`type`, `b`.`name_japanese`, `a`.`amount`, `a`.`time`, `c`.`char_id` 
FROM (`gravity`.`picklog` `a` INNER JOIN `gravity`.`item_db2` `b` ON `a`.`nameid` = `b`.`id` ) 
INNER JOIN `gravity`.`char` `c` ON `c`.`char_id` = `a`.`char_id` 
WHERE (`b`.`name_japanese` LIKE '%apple%' OR `a`.`nameid` = 'apple') 
ORDER BY 5 DESC LIMIT 0,20

 

it takes just as long, and the same goes for this: (search by name with no item)

SELECT `c`.`name`, `a`.`type`, `b`.`name_japanese`, `a`.`amount`, `a`.`time`, `a`.`char_id` 
FROM (`gravity`.`picklog` `a` INNER JOIN `gravity`.`item_db` `b` ON `a`.`nameid` = `b`.`id` ) 
INNER JOIN `gravity`.`char` `c` ON `c`.`char_id` = `a`.`char_id` 
WHERE (`c`.`char_id` = 'Chaos' OR `c`.`name` LIKE CONVERT( _utf8 '%Chaos%' USING latin1 )) 
UNION 
SELECT `c`.`name`, `a`.`type`, `b`.`name_japanese`, `a`.`amount`, `a`.`time`, `c`.`char_id` 
FROM (`gravity`.`picklog` `a` INNER JOIN `gravity`.`item_db2` `b` ON `a`.`nameid` = `b`.`id` ) 
INNER JOIN `gravity`.`char` `c` ON `c`.`char_id` = `a`.`char_id` 
WHERE (`c`.`char_id` = 'Chaos' OR `c`.`name` LIKE CONVERT( _utf8 '%Chaos%' USING latin1 ))
ORDER BY 5 DESC LIMIT 0,20

 

when i removed the like's it ran faster but didn't pull what i needed >.>

 

should i keep tearing it apart?

 

and as for the % thing that is so they can do partial matches (makes it easier when you don't remember someones full name  )

[fenway]

Let's see some of the EXPLAIN output on these queries.

[Ryokotsusai]

I have never used EXPLAIN before... but here is what it gave me for each of the queries (thru PMA):

 

Original Query

Search Only Item

Search Only Name

Full search Only Item

[fenway]

What's with ORDER BY 5?

[Ryokotsusai]

I needed the results ordered by time, and ORDER BY `a`.`time` just gave me errors, so I am ordering by the 5th place in each query, which is a.time in both

[btherl]

Could that error be because the union eliminated table names?  In which case you should just order by time instead of a.time

 

Regarding the explain output, I'm a bit of a noob at reading mysql explains, so I'll leave that to the experts..

[Ryokotsusai]

Well that solved the ORDER BY thing, but i didn't even think that was really a problem