搜索页面的SELECT语句

Question

I'm trying to speed up my SELECT statement

The KEYS:

• a.id (Primary)
• b.id (Index)
• c.id (Unique)
• d.id (Unique)
• e.id (Index)
• a.Kid (Index)
• b.stock (Index)
• b.warehouse (Index)

The SELECT statement:

SELECT DISTINCT(a.id), a.name, c.name, c.description
FROM table1 a 
LEFT JOIN table2 b ON a.id=b.id 
LEFT JOIN table3 c ON a.id=c.id
LEFT JOIN table4 d ON a.id=d.id 
LEFT JOIN table5 e ON a.Kid=e.id 
WHERE a.Act='T' 
AND b.stock!=0 
AND b.warehouse IN ('LJ0001','MS0001') 
AND e.internet_display=-1 
AND a.name LIKE '%some text%' 
OR a.Act='T' 
AND b.stock!=0 
AND b.warehouse IN ('LJ0001','MS0001') 
AND e.internet_display=-1 
AND c.name LIKE '%some text%' 
OR a.Act='T' 
AND b.stock!=0 
AND b.warehouse IN ('LJ0001','MS0001') 
AND e.internet_display=-1 
AND a.id LIKE '%some text%' 
OR a.Act='T' 
AND b.stock!=0 
AND b.warehouse IN ('LJ0001','MS0001') 
AND e.internet_display=-1 
AND a.EANcode LIKE '%some text%' 
OR a.Act='T' 
AND b.stock!=0 
AND b.warehouse IN ('LJ0001','MS0001') 
AND e.internet_display=-1 
AND c.id LIKE '%some text%' 
OR a.Act='T' 
AND b.stock!=0 
AND b.warehouse IN ('LJ0001','MS0001') 
AND e.internet_display=-1 
AND a.name LIKE '%some%' 
AND a.name LIKE '%text%' 
OR a.Act='T' 
AND b.stock!=0 
AND b.warehouse IN ('LJ0001','MS0001') 
AND e.internet_display=-1 
AND c.name LIKE '%some%' 
AND c.name LIKE '%text%' 
ORDER BY d.views DESC, a.name 
LIMIT 30

• What can i do to make it better/faster ?
• Add index keys ?
• Change the WHERE part of the statement ?
• Add something to the SELECT statement ?

This SELECT statement is being used when someone types into the search box. Which means the WHERE part changes dynamically.

More info:

Table meanings and usige:

• table1 (table of all the products)
  • used for searching name, EANcode and ID of product
• table2 (table of stocks for products)
  • used for removing products that are not in warehouse MS0001 and LJ0001
  • used for removing products that have no stock
• table3 (table of more info)
  • used for searching second name of product
• table4 (table of views per product)
  • used for sorting only
• table5 (table of categorys for products)
  • used for removing products which are in a category that is hidden

Answer 1

The only way to know what is making a query slow is to ask the database to explain what it is doing since it depends on the size of the data in it. A full table-scan on a table with 4 rows take no time so the db will never use an index, scanning the same table with 4 million rows takes much more time so the database will look for any index to use.

You can put explain in front of your query and run it to see what it is doing and see if you can find which part to start with improving. Remember that too many indexes will make insert slow.