Creating a quicker MySQL Query

Question

I'm trying to create a faster query, right now i have large databases. My table sizes are 5 col, 530k rows, and 300 col, 4k rows (sadly i have 0 control over architecture, otherwise I wouldn't be having this silly problem with a poor db).

SELECT  cast( table2.foo_1 AS datetime ) as date,
        table1.*, table2.foo_2, foo_3, foo_4, foo_5, foo_6, foo_7, foo_8, foo_9, foo_10, foo_11, foo_12, foo_13, foo_14, foo_15, foo_16, foo_17, foo_18, foo_19, foo_20, foo_21
FROM    table1, table2
WHERE   table2.foo_0 = table1.foo_0
        AND table1.bar1 >= NOW()
        AND foo_20="tada"
ORDER BY
        date desc
LIMIT   0,10

I've indexed the table2.foo_0 and table1.foo_0 along with foo_20 in hopes that it would allow for faster querying.. i'm still at nearly 7 second load time.. is there something else I can do?

Cheers

Answer 1

table2 needs a compound index on foo_0, foo_20, and bar1.

Answer 2

I think an index on bar1 is the key. I always run into performance issues with dates because it has to compare each of the 530K rows.

Answer 3

Indexing table1.bar1 may improve the >=NOW comparison. A compound index on table2.foo_0 and table2.foo_20 will help. An index on table2.foo_1 may help the sort.

Overall, pasting the output of your query with EXPLAIN prepended may also give some hints.

Answer 4

An index on table1.foo_0, table1.bar1 could help too, assuming that foo_20 belongs to table1.

See How to use MySQL indexes and Optimizing queries with explain.

Use compound indexes that corresponds to your WHERE equalities (in general leftmost col in the index), WHERE commparison to abolute value (middle), and ORDER BY clause (right, in the same order).

Answer 5

Create the following indexes:

CREATE INDEX ix_table1_0_1 ON table1 (foo_1, foo_0)
CREATE INDEX ix_table2_20_0 ON table2 (foo_20, foo_0)

and rewrite you query as this:

SELECT  cast( table2.foo_1 AS datetime ) as date,
        table1.*, table2.foo_2, foo_3, foo_4, foo_5, foo_6, foo_7, foo_8, foo_9, foo_10, foo_11, foo_12, foo_13, foo_14, foo_15, foo_16, foo_17, foo_18, foo_19, foo_20, foo_21
FROM    table1
JOIN    table2
ON      table2.foo_0 = table1.foo_0
        AND table2.foo_20 = "tada"
WHERE   table1.bar1 >= NOW()
ORDER BY
        table1.foo_1 DESC
LIMIT   0, 10

The first index will be used for ORDER BY, the second one will be used for JOIN.

You, though, may benefit more from creating the first index like this:

CREATE INDEX ix_table1_0_1 ON table1 (bar, foo_0)

which may apply more restrictive filtering on bar.

I have a blog post on this:

• Choosing index

, which advices on how to choose which index to create for cases like that.