2

Im getting very confused with indexes in MySQL.

I have two tables: TableA1 and TableA2.

I created indexes on these for the joins between them and queries run really fast.

I have another 2 tables with an identical setup e.g. TableB1 and TableB2. The only difference is that these tables have a few null values.

For some reason the same query on TableA is about 5 times faster and doesn't require a full-table scan. TableB however seems to use a full table scan and I can't work out why. Could it be the Null values that are causing this?

I notice using EXPLAIN that on the TableA setup, I get possible keys: myindex and also ref: func; however on the TableB setup I get just possible keys: NULL and ref: NULL.

I have searched around for quite a while on this, and cannot seem to find a relevant answer. Would appreciate if someone can point me in the right direction.

(Sorry, now added to the original question.)

Here is TableAOne:

CREATE TABLE `TableAOne` (
  `field1` varchar(255) DEFAULT NULL,
  `field2` varchar(255) DEFAULT NULL,
  KEY `myindex` (`field1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And TableATwo:

CREATE TABLE `TableATwo` (
  `Field3` varchar(255) ,
  `Field4` varchar(255) ,
  `Field5` varchar(255) ,
  `id` int(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=15522 DEFAULT CHARSET=utf8;

I am joining Field3 with Field1.

The tables that get the full-table scan are identical to the above tables. The only difference is they are called TableB instead of TableA.

Cheers,
Ke

Ke.
  • 2,484
  • 8
  • 40
  • 78
  • 1
    Can you post your create table scripts and perhaps also a row count from each table? – Mark Byers Feb 14 '10 at 19:44
  • 1
    Is `NULL` a valid value to link between the B tables? If not, exclude them using `AND b2.col IS NOT NULL` in the JOIN criteria, and `WHERE b1.col IS NOT NULL` – OMG Ponies Feb 14 '10 at 19:45
  • yep null is a valid value, there are some empty values too (not sure if this is wise!!), I would like to keep the null values and empty ones, I will post the create tables in a moment too – Ke. Feb 14 '10 at 19:53
  • Two "identical" tables suggests that you aren't normalized properly. Are we to interpret that to be literally true? If yes, why is it necessary? – duffymo Feb 14 '10 at 19:54
  • sorry added above, didnt realise i should edit my own question to add code – Ke. Feb 14 '10 at 20:03
  • I have tried analyzing the tables and also optimising them. Is there anything else I can do? TableAOne and TableATwo dont have any null values, but TableBOne and TableBTwo do. – Ke. Feb 14 '10 at 20:08
  • @Ke: tried to fix the formatting a bit. You can "rollback" to a previous version if you don't like what I've done. In addition, for the SQL you can highlight and press Ctrl-K to format using a monospace font. Welcome to SO. – mechanical_meat Feb 14 '10 at 20:16
  • i like, thx Adam, im just learning the power of this great site :) duffymo - by normalised, do you mean not having null/empty values? – Ke. Feb 14 '10 at 20:17
  • 1
    Please re-edit your question and include the queries and the explain plan. ALso it is not very clear to me whehter you posted all table definitions involved. Side note: i am noticing one of your tables is InnoDB and the other is MyISAM, perhaps the difference between the "identical" tables is that? – Roland Bouman Feb 14 '10 at 21:00
  • Yes, please include the queries and explain plan. – ysth Feb 14 '10 at 21:05
  • will do - i dont think its the myisam/innodb problem, because both sets of tableA and tableB are identical, its going to take me a little while to put this together, thankx for all of your helps - i really appreciate this. – Ke. Feb 14 '10 at 21:10

1 Answers1

0

I just deleted the table and re-installed it to the db (i did change utf8 to latin1 not sure if this made a difference),however it works now, really strange! thanks v much for all the replies they certainly pushed me towards the answer cheers :)

Ke.
  • 2,484
  • 8
  • 40
  • 78