0

I wrote codes to search for a keyword from different tables. This is my code :

$stmt = $conn->prepare("SELECT DISTINCT rest.*,
MATCH(rest.resname, rest.address) AGAINST(:keyword) as a,
MATCH(cuisine.cuisine) AGAINST(:keyword) as b,
MATCH(recc.recc) AGAINST(:keyword) as c,
MATCH(emenu.menu, emenu.menudesc) AGAINST(:keyword) as d,
MATCH(facilities.fac) AGAINST(:keyword) as e,
MATCH(serv.service) AGAINST(:keyword) as f
FROM rest
INNER JOIN cuisine ON rest.resID = cuisine.resID
INNER JOIN recc ON rest.resID = recc.resID
INNER JOIN emenu ON rest.resID = emenu.resID
INNER JOIN facilities ON rest.resID = facilities.resID
INNER JOIN serv ON rest.resID = serv.resID
WHERE
(
MATCH(rest.resname, rest.address) AGAINST(:keyword)
OR MATCH(cuisine.cuisine) AGAINST(:keyword)
OR MATCH(recc.recc) AGAINST(:keyword)
OR MATCH(emenu.menu, emenu.menudesc) AGAINST(:keyword)
OR MATCH(facilities.fac) AGAINST(:keyword)
OR MATCH(serv.service) AGAINST(:keyword)
)
AND (aktif = :aktif) AND (negeri = :negeri) GROUP BY resID ORDER BY resID DESC LIMIT :rowsperpage OFFSET :offset");

$stmt->bindValue(':keyword', $_GET["keyword"], PDO::PARAM_STR);
$stmt->bindValue(':negeri', $negeri, PDO::PARAM_STR);
$stmt->bindValue(':aktif', $active, PDO::PARAM_STR);
$stmt->bindValue(':rowsperpage', (int) $rowsperpage, PDO::PARAM_INT);
$stmt->bindValue(':offset', (int) $offset, PDO::PARAM_INT);
$stmt->execute();

There were 6 tables involved in this query.

When I run this, the search runs so slow.

Can you tell me how to solve this? and how do you create index in phpMyAdmin table?

Redzwan Latif
  • 886
  • 3
  • 14
  • 38
  • have you profiled your query? try adding EXPLAIN to the start of it. See where its hanging up. Have you got index's correctly specified against all your join targets? – Dave Nov 29 '13 at 10:10
  • @Dave putting EXPLAIN does not return anything. How to add index into the column via phpmyadmin? – Redzwan Latif Nov 29 '13 at 10:18
  • Profiling your statement can be done like this http://www.jetprofiler.com/ http://stackoverflow.com/questions/12925140/how-to-estimate-sql-query-timing http://dev.mysql.com/doc/refman/5.5/en/show-profile.html – Dave Nov 29 '13 at 10:24
  • Using phpmyadmin just goto each table structure view so in your case goto cuisine and on the cuisine resID colum one of the little icons will be create index click it let it create index then goto next table and repeat. Also make sure your rest.resID column is your primary key etc – Dave Nov 29 '13 at 10:25
  • Before this, I've indexed al the columns instead of indexing the resID column as you said. How do I revert back all the index? Thank you :D – Redzwan Latif Nov 29 '13 at 10:32
  • phpmyadmin has an index list on the structure view you can add and delete the index's on there. You need to make sure that any columns involved in a join are indexed and it'll speed you query up massively! – Dave Nov 29 '13 at 10:48

0 Answers0