Question 394392 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Query does not use index

I have this query:

Explain 
select SQL_NO_CACHE p.id as product_id, 
      p.price_amount, 
      p.is_male, 
      p.is_female, 
      p.is_accessories, 
      p.is_contact_lens, 
      c.id as color_id, 
      b.name as brand, 
      p.name, 
      c.color, 
      c.code, 
      c.pohoda_sku, 
      c.on_stock, 
      c.not_send_to_kiosks, 
      (p.is_active && !p.is_archive && c.is_active && !c.is_archive) as is_active, 
      c.is_visible, 
      c.abbr 
from product_colors as c 
left join products as p 
      on p.id = c.product_id 
left join brands as b 
      on p.brand_id = b.id 

order by c.on_stock desc

enter image description here

Why the query does not use index in first table? In phpmyadmin it durate 0.0708s, but on the web it is more than 1s. I do not want limit it by where or limit. Can I optimize this query or it is cause with some memory limit in php.ini or elsewhere?

Tags:

2

Answers


  1. 0

    MySQL can only use the index with

    ORDER BY c.on_stock DESC

    if it was created that way.

    CREATE INDEX idx_desc_columns ON product_colors ( on_stock DESC);

    try this:

    select p.id as product_id, p.price_amount, p.is_male, p.is_female, p.is_accessories, p.is_contact_lens, c.id as color_id, b.name as brand, p.name, c.color, c.code, c.pohoda_sku, c.on_stock, c.not_send_to_kiosks, (p.is_active && !p.is_archive && c.is_active && !c.is_archive) as is_active, c.is_visible, c.abbr 
from ( SELECT * FROM product_colors order by on_stock desc) as c 
left join products as p on p.id = c.product_id 
left join brands as b on p.brand_id = b.id;

    You can also change the * in the select to the fields you are using.

    A other case

    There are also other possibilities why the optimiter decides not to take an index. This is the case, for example, if the sort_buffer_size is configured too small.

    You can query these like this

    SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'innodb_sort_buffer_size';

    and then set it temporarily. When enlarging, make sure that there is enough memory available.

    SET GLOBAL sort_buffer_size = 4194304;  -- 4 MB or more
SET GLOBAL innodb_sort_buffer_size = 1048576;  -- 1 MB or more

    If this is successful, you can also make these changes in my.ini as follows.

    [mysqld]
sort_buffer_size = 4M
innodb_sort_buffer_size = 1M
    Login or Signup to reply.
  2. 0

    SQL_NO_CACHE are disables caching, potentially decreasing execution time.
    try not using SQL_NO_CACHE.

    also try increasing the memory_limit in php.ini configuration because the hosting server architecture is different from your local server

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search