CODEWITHSUNDEEP
phpmysql
Marko Mihalič
Marko Mihalič

Reputation: 47

MySQL, How can i make this query faster?

I'm working on a query that needs to connect 4 large tables.

The tables:

Table_1 (ID, active, price1, price2, name, category1, category2) This is the main table that has all the products, 32561 rows

table_2 (ID, table_1_ID, room, stock) This is the table that contains the stock of the products in table_1, this table can have table_1.ID multiple times, 190400 rows

table_3 (ID, table_1_ID, images) This table contains more info about a product like images, some products don't have them, 6546 rows

table_4 (table_1_ID, view, type) This table contains views of products (views per month)

What i want:

I'm trying to make a query that displays 20 of the top viewed products.

Conditions:

  1. It can only display products that are in table_1.category1 (A,B,C,D,E)
  2. It can only display products that are active='T'
  3. It can only display products that are in room LJ0001 and MS0001
  4. It can only display products that have at least 1 stock

The code:

SELECT DISTINCT(Table_1.ID), Table_1.price1, Table_1.price2
   , Table_1.name, Table_1.category2, Table_3.images 
FROM Table_1 
   LEFT JOIN Table_2 ON Table_1.ID=Table_2.table_1_ID 
   LEFT JOIN table_3 ON table_1.ID=table_3.table_1_ID 
   LEFT JOIN table_4 ON table_1.ID=table_4.table_1_ID 
WHERE Table_1.active='T' 
   AND (Table_1.category1 LIKE 'A%' 
        OR Table_1.category1 LIKE 'B%' 
        OR Table_1.category1 LIKE 'C%' 
        OR Table_1.category1 LIKE 'D%' 
        OR Table_1.category1 LIKE 'E%'
       ) 
   AND table_2.room IN ('LJ0001','MS0001') 
   AND table_2.stock!=0 
ORDER BY table_4.view DESC, Table_1.name 
LIMIT 10 OFFSET 0

Note:

The code above works. But it take a while. I'm looking for any fixes that could make this faster.

Any help would be nice! Thanks in advance.

EXPLAIN EXTENDED

Link to image

Upvotes: 1

Views: 93

Answers (2)

marcus
marcus

Reputation: 651

I think my fixes can help you. if you can set sql_cache_type = 2 in my.cnf you should use SQL_CACHE in query (if there are not fields with text type) and it is better first do conditions which are "easier" for DB (LIKE with OR are very slow), if you use INNODB you can set a larger buffer pool for MySQL

SELECT SQL_CACHE DISTINCT(Table_1.ID), Table_1.price1, Table_1.price2
, Table_1.name, Table_1.category2, Table_3.images 
FROM Table_1 
LEFT JOIN Table_2 ON Table_1.ID=Table_2.table_1_ID 
LEFT JOIN table_3 ON table_1.ID=table_3.table_1_ID 
LEFT JOIN table_4 ON table_1.ID=table_4.table_1_ID 
WHERE Table_1.active='T' 
 AND table_2.stock!=0 
 AND table_2.room IN ('LJ0001','MS0001') 
 AND (Table_1.category1 LIKE 'A%' 
    OR Table_1.category1 LIKE 'B%' 
    OR Table_1.category1 LIKE 'C%' 
    OR Table_1.category1 LIKE 'D%' 
    OR Table_1.category1 LIKE 'E%'
   ) 
ORDER BY table_4.view DESC, Table_1.name 
LIMIT 10 OFFSET 0

Upvotes: 0

Roman
Roman

Reputation: 394

Use the updated query below:

EXPLAIN EXTENDED
SELECT Table_1.ID, Table_1.price1, Table_1.price2
, Table_1.name, Table_1.category2, Table_3.images 
FROM Table_1 
LEFT JOIN Table_2 ON Table_1.ID=Table_2.table_1_ID 
LEFT JOIN table_3 ON table_1.ID=table_3.table_1_ID 
LEFT JOIN table_4 ON table_1.ID=table_4.table_1_ID 
WHERE Table_1.active='T' 
AND (Table_1.category1 = 'A' 
    OR Table_1.category1 = 'B' 
    OR Table_1.category1 = 'C' 
    OR Table_1.category1 = 'D' 
    OR Table_1.category1 = 'E'
    ) 
AND table_2.room IN ('LJ0001','MS0001') 
AND table_2.stock != 0 
ORDER BY table_4.view DESC, Table_1.name 
LIMIT 10 OFFSET 0

I deleted DISTINCT because I assumed that table_1.ID is a primary key. If so,there is no need to use DISTINCT with primary keys because it's already distinct. I also changed the LIKE operator to = it because it works faster.

Then you need to make sure that all join fields such as table_1_ID have been indexed.

And you may notice EXPLAIN EXTENDED on the top of the query - with this clause you will get something like this from the db:

           id: 1
           select_type: PRIMARY
           table: t1
           type: index
           possible_keys: NULL
           key: PRIMARY
           key_len: 4
           ref: NULL
           rows: 4
           filtered: 100.00
           Extra: Using index

You will get the description for each table. All foreign keys used in the query will be listed, including details like if the foreign key has index, etc. You will find more info here.

Upvotes: 1

Related Questions