CODEWITHSUNDEEP
mysqlsearch
user1822357
user1822357

Reputation:

How to speed up mysql search

I am using this code for reading from the db, but the results that I get are kind of slow. So I need some help about that problem.

SELECT mod_part.id AS mp_id,parts.id as part_id,outlets.name as out_name, parts.part_name as part_name, parts.brand, outlets.sell, outlets.cost, outlets.qty, outlets.comp, parts.supplier, brands.name AS brand_name 
FROM outlets,models,manufacturers,brands,parts,mod_part 
WHERE outlets.part_id=mod_part.id 
      AND mod_part.part_id=parts.id 
      AND brands.id=mod_part.brand_id 
      AND mod_part.model_id=models.id 
      AND parts.man_id=manufacturers.id 
      AND (MATCH (manufacturers.name) AGAINST ('".$_GET['search']."') 
      OR MATCH (parts.part_name) AGAINST('".$_GET['search']."') 
      OR MATCH(models.name) AGAINST('".$_GET['search']."')) 
GROUP BY parts.id

Upvotes: 1

Views: 249

Answers (2)

echo_Me
echo_Me

Reputation: 37233

you can try this with inner join

$search = mysql_real_escape_string($_GET['search']);  // escape from sql injection here

and then use it in your sql (please use PDO or SQLI instead)

"SELECT mo.id AS mp_id,p.id as part_id,o.name as out_name,p.part_name as part_name,p.brand,o.sell,o.cost,o.qty,o.comp,p.supplier,b.name AS brand_name 
 FROM outlets 
 INNER JOIN models m  ON  m.id = mo.model_id
 INNER JOIN parts p ON  mo.part_id= p.id 
 INNER JOIN manufacturers ma ON p.man_id=ma.id 
 INNER JOIN brands b ON  bid=mo.brand_id
 INNER JOIN mod_part mo ON mo.part_id=mo.id 

WHERE 
   (MATCH (ma.name)  AGAINST ('".$search."')
   OR MATCH (p.part_name)  AGAINST('".$search."') 
   OR MATCH(m.name)  AGAINST('".$search."'))
GROUP BY p.id ";

Upvotes: 1

gpojd
gpojd

Reputation: 23065

If the query is slow, try looking at the explain plan. Otherwise, you'll have to give more information, such as table structure, indexes, code, etc.

Also, you are allowing SQL injection. Please read up on that subject and fix the code. If you tell which language you are using, I can try to add some links. Otherwise, look into "binding" your variables.

Upvotes: 0

Related Questions