MySQL: nested select speed problem

I have follow tables:

|ELEMENTS|
------------
|id_element|
|id_catalog|
|value|


|CATALOG|
------------
|id_catalog|
|catalog_name|
|show|
|status|

I tried to add different indecies (several variants):

1) ELEMENT: pair(id_element, id_catalog) and id_element and id_catalog
2) ELEMENT: pair(id_element, id_catalog) and id_element
3) ELEMENT: pair(id_element, id_catalog) and id_catalog
4) ELEMENT: id_element and id_catalog

1) CATALOG: pair(show, status) and id_catalog
2) CATALOG: id_catalog and show and status

Execute follow select:

SELECT DISTINCT `id_element` FROM `ELEMENTS`
       WHERE (id_catalog IN (SELECT `id_catalog` FROM `CATALOG` WHERE status=1 AND show = 1)) limit 10

If there开发者_运维技巧 are some rows then it works very fast. But if it is empty - it takes more than 4 sec.

At the same time "SELECTid_catalogFROMCATALOGWHERE status=1 AND show = 1" works fast both there are some rows and empty.

In the table ELEMENTS there are 100.000 records In the table CATALOG there are 15.000 records

Also I tried "join" but it takes more time than it was before.

Why empty query works so long and what I should do to increase speed rate?

Here are explain answer:

id | select_type          | table                  | type              | possible_keys             | key        | key_len | ref    | rows   | Extra
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1  | 'PRIMARY',           |'ELEMENTS'              | 'index'           | ''                        | null       | null    | null   | 270044 | 'Using where; Using temporary'
2  | 'DEPENDENT SUBQUERY' | 'CATALOG'              | 'unique_subquery' | 'PRIMARY,pair,id_catalog' | 'PRIMARY'  | '4'     | 'func' | 1      | 'Using where'

I guess indexing CATALOG(status,show) would allow a quick answer to the sub-select.

And then some index on ELEMENTS(id_catalog) would speed up the answer to the main question.

Maybe it depends on the statistics on these columns: it they are no selective enough, you'll end up with many rows anyway.

Could you show the output of EXPLAIN when using the two indexes above?

Why not simply writing a join to help the optimizer do its job?

SELECT DISTINCT id_element
FROM elements JOIN catalog ON elements.id_catalog=catalog.id_catalog
WHERE status=1 AND show = 1
LIMIT 10

(untested)

Well, the reason you're having the problem is that you're pulling up the entire catalog database for each request and finding every match between the element and the catalog. If MySQL finds 10 entries, it bails out, but if it never finds them it will continue to check your entire database. I would use an EXISTS query to try and get some performance increase.

SELECT DISTINCT(e.id_element)
FROM ELEMENTS e
WHERE EXISTS (
    SELECT *
    FROM CATALOG c
    WHERE c.id_catalog = e.id_catalog
    AND c.status = 1
    AND c.show = 1)
LIMIT 10;

This will decrease the amount of time MySQL spends looking for the catalog for each element by imposing a LIMIT 1 on the inner query, but you always run the risk of a long search time when there are possibly no matches.

I would put these indices there:

CREATE INDEX idx_element_1 ON ELEMENT (id_catalog);
CREATE INDEX idx_catalog_1 ON CATALOG (status, show);

Also these, although they might not be needed for your query (these should probably be primary keys, unless you have duplicates):

CREATE INDEX idx_element_2 ON ELEMENT (id_element);
CREATE INDEX idx_catalog_2 ON CATALOG (id_catalog);

Could you drop other indices and create these and check back with the query results?

Thx to all. I solved it by table denormalization. Because there are too much data in this dables which are separated. I decided to combine it to one table. And now it works perfect. Now query always takes 0.03 second.