I have a mediawiki for a game and I thought it would be a good idea to add a search extension that along with regular wiki search result would look and display any potential entries in the game's item DB. For example, someone searches for 'Gold Coin' and the same page displays all articles containing the word 'Gold Coin' and the item DB search result. And that actually works, sometimes.
https://serwer1530566.home.pl/powerbook/index.php?title=Special%3ASearch&search=gold+coin&go=Go
From time to time it will just resturn:
"Query to get data from items failed: Lost connection to MySQL server during query"
$mysqlserver="localhost";
$mysqlusername="db";
$mysqlpassword="password";
$link3=mysql_connect($mysqlserver, $mysqlusername, $mysqlpassword) or die ("Error connecting to mysql server: ".mysql_error());
$dbname = 'db';
mysql_select_db($dbname, $link3) or die ("Error selecting specified database on mysql server: ".mysql_error());
mysql_set_charset('utf8', $link3);
$dbquery="SELECT translations.name, body, items.id, items.quality, items.icon_name, items.level, client_version.client_version FROM items, translations, client_version WHERE items.id=client_version.id AND body LIKE '%$locavariable%' AND translations.name NOT LIKE '%_desc' AND items.description=translations.name AND language = 'EN' ";
$locavariable is the searching term
I have tried using LIMIT, nothing.
I thought the 'translations' table is too large (there are 3mln entries there) but when I used a much smaller version it was giving the same error.
In most cases it's working fine, I can even do a total wildcard search and it will return all 100k items without losing connection but from time to time it just dies.
Any ideas/suggestions?
Update: I changed structure of the translation table and the query to:
SELECT
client_version,
i.id id,
i.level level,
i.quality quality,
i.icon_name icon_name,
t.LAN_EN EN
FROM client_version, items i
LEFT JOIN translation_small t ON t.name = i.description
WHERE client_version.id=i.id AND t.LAN_EN LIKE 'Gold Coin'
DB structure:
================================= =====================================================================
| name | LEN_EN | | ID | level | quality | icon_name | description |
================================= =====================================================================
| STR_QUEST_2258a | Gold Coin | | 182203239 | 1 | common | Icon_Item_Qina01 | STR_QUEST_2258a |
================================= =====================================================================
DB EXPLAIN
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
=========================================================================================================================================
| 1 | SIMPLE | i | ALL | PRIMARY | NULL | NULL | NULL | 101588 | |
=========================================================================================================================================
| 1 | SIMPLE | client_version | eq_ref | PRIMARY | PRIMARY | 4 | 18096496_db.i.id | 1 | |
=========================================================================================================================================
| 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 767 | 18096496_db.i.description | 1 | Using where |
=========================================================================================================================================