Table data increased - query is slow.

Question

Hi,

I have mysql DB in which a single table has 7 million records, the table needs to be optimized, i am little bit hesitant to add index as that is going to take much time to add across 7 million records. I keep getting this table queries in slow query log, along the query is written in optimistic manner.

What is the best approach to handle this so that table query does not come up in slow query?

Answer 1

Without your code, there is no definitive help. Indexing is the way to go, and it will take a little time, but it is only once. It does add overhead to your inserts, but that is the price to pay for increased query efficiency.

Answer 2

You need to create an index. No, really. Maybe even more than one.

Queries against 7 million rows without an index is going to be slow. You could split up the table or allocate a huge amount of memory for caching, but indexes give you far more bang for the buck.

You may ask, which index(es) should you create? Good question. That depends on the specific queries you run against the table. Table design is determined by your data, but optimization is determined by your queries.

Learn to use EXPLAIN. See Explain Demystified.

Learn how indexes work. See More Mastering the Art of Indexing

---

Re your comment: Yes, a primary key implicitly has an index (at least in MySQL), but I have no idea if the query you need to improve benefits from that index.

Here's a tip: when I want to experiment with indexes on a really large table I make a copy table from a subset of the rows.

mysql> create table test.mytable as select * from realdb.mytable limit 10000;
mysql> use test;

Put a nontrivial number of rows into the table, but few enough that working on it won't take too long.

Now you can create and drop indexes and test out the queries to see how they perform. Once you have identified the index or indexes that give you the most benefit, you can have more confidence that creating them on your real database is worth it.

Answer 3

Delete from table1 where column1 in (select column1 from table2); is not optimal.

Try

delete table1 t1 
from table1 t1 
inner join table2 t2 on t2.column1 = t1.column1 
where conditions=true;

I believe it should perform better