0

I found a lot of information on how indexes works in MySQL by looking at the following SO link: How do MySQL indexes work? However, I am facing a mysql issue I can not resolve, and I'm unsure whether it is related to indexing or not.

The problem is: I used multiple indexes in most of my tables, and everything seems to be working fine. However, when I restore the old back up data to my existing data, the size of the db keeps getting larger (it almost doubles each time).

Example: I was using a mysql db named DB1 last week, I made a backup and continued to use DB1. A few days later, I needed to continue from that backup db, so I restored it to DB1.

Before the restore, DB1's size was 115MB, but afterward it was suddenly 350MB.

Can anyone help shed some light on what might be happening?

Community
  • 1
  • 1
Sensa
  • 383
  • 3
  • 6
  • 20
  • 2
    How are you measuring the "*database size*"? – eggyal Dec 21 '12 at 17:57
  • 1
    Are you droping your database and recreating it before restoring? – Pablo Santa Cruz Dec 21 '12 at 17:58
  • You might need to define UNIQUE constraint so that duplicates aren't added. Is that what you're trying to do? http://www.w3schools.com/sql/sql_unique.asp – zer0bit Dec 21 '12 at 17:59
  • You need to indicate how you are measuring database size. If you are looking at innodb storage files, they will keep increasing even if you delete or remove data. – datasage Dec 21 '12 at 18:42
  • I am using HeidiSQL (UI) in which i can able to see the size of DB. the current fetching speed is good. But the size is confusing me. For your information: While restoring the DB i used drop table, create table and insert data.. – Sensa Dec 21 '12 at 19:55

1 Answers1

0

This is not surprising. If you have lots of indexes, it's not unusual for them to take up as much space as the data itself.

When you are talking about 115MB vs. 350MB though, I'd guess the increase in query speed you get is probably worth that extra couple hundred megs of disk space. If not, then you might want to take a closer look at your indexes and make sure they are all actually providing some benefit.

Eric Petroelje
  • 59,820
  • 9
  • 127
  • 177
  • Thanks for your reply. I am using HeidiSQL (UI) in which i can able to see the size of DB. And i am satisfied with the current speed too. But the size is confusing me. For your information: While restoring the DB i used drop table, create table and insert data... – Sensa Dec 21 '12 at 19:53
  • 1
    @Sensa - will that tool allow you to see the data and index size separately for each table? If not, try using the `show table status` command, looking at the `data_length` and `index_length` columns should give you a good idea of how much space is being used for data vs. indexes. – Eric Petroelje Dec 21 '12 at 20:10