Just did a big no no with MySQL?

Lt. Manlove · 21 Aug 2007 at 13:21

The table had already about 2000 rows, all with an index key.

We imported 6000 more rows starting with an index of 1 000 000. The queries are now a lot slower.

Is this due to MySQL searching over 1 million indexes rather than the 8000 it should? And is there a way to fix this?

Cheers

Dj_Jestar · 21 Aug 2007 at 13:48

No, it won't be searching over 1mill, it's just simply a lot of records, and large indexes, and a large gap may not help either.

Next time, use an insert such as:

Code:

INSERT INTO `desttable` (`every`, `column`, `except`, `for`, `the`, `autoincrement`, `index`) FROM (SELECT `every`, `column`, `except`, `for`, `the`, `autoincrement`, `index` FROM `sourcetable`);

Lt. Manlove · 21 Aug 2007 at 14:19

So it probably is or isn't the large gap from 2000 - 1000000 ?

I'm just wondering why my queries are taking a while to execute and that seems like the obvious answer.

rpstewart · 21 Aug 2007 at 15:03

How much longer are they taking? You've got four times the data so it's not unexpected that the queries are taking longer.

I'm not a MySQL expert but with DB2 when you start increasing volumes like that it can throw the query optimiser off and you end up with it using inefficient access plans. Is there an equivalent of a runstats command in MySQL to update the table statistics?

Moredhel · 21 Aug 2007 at 16:07

stick explain before your query and run it in the mysql console. It should give you pointers as to what it's doing.

Sic · 21 Aug 2007 at 16:41

might be a stupid question, but have you got indexes on all columns that need it? it's great for performance to index every column that will be searched, not just the PK

Dj_Jestar · 21 Aug 2007 at 17:10

Lt. Manlove said:
So it probably is or isn't the large gap from 2000 - 1000000 ?

I'm just wondering why my queries are taking a while to execute and that seems like the obvious answer.

Have you tried count()ing the rows to find out?