Hi:
I have following table:
create table instituteEmail (
id int auto_increment primary key,
name varchar(50) not null unique,
idInstitute int references instituteInfo(id),
isActive tinyint not null default 1,
index(name,idInstitute)
);
I have 4 rows in it.
When I “explain” following Select command,
describe SELECT instituteInfo.id, instituteInfo.name, instituteInfo.email, instituteInfo.idType, instituteInfo.idInstitute, instituteInfo.isActive FROM instituteInfo WHERE (name like ‘%%’) AND (idInstitute = 0) LIMIT 4
It shows “type=all”
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE instituteInfo ALL NULL NULL NULL NULL 4 Using where
Can someone please help me on how can I improve? From what i read, “type=all” is bad. Is it true in this case too?
thakns
If you don’t have that many records in total, mysql won’t scan index tree. Seeing your explain, you only have 4 records so mysql will choose to go for full table scan rather than using index file as well. Your query also isn’t suitable for use of indexes, as you have LIKE %% which forces mysql to perform a full table scan.
However, you have indexed 3 out of 4 columns of your table. Whether it’ll be a full table scan or not, it doesn’t really matter as the performance will be nearly the same.
Thanks. So, if I din’t have like and had 1000 column then it should kick in?
from that perspective, is that index good?
Nope, with query like that the index won’t help.
Can you please clarify?
Is there anything that can be done to improve this? or will I have to leave it like this?
Of course.
First off, you are not selecting a single thing from your instituteEmail table so the SHOW CREATE is not really helpful there since we’re looking at the wrong table, as you’re using instituteInfo table.
Second thing is that your WHERE clause is forcing MySQL to perform full table scan due to LIKE ‘%%’.
As I don’t really know what your end goal is (I could be guessing), until you explain what you want to achieve - it’s hard to suggest optimization plan.
Also, indexes aren’t that magic. You can’t just index a column and then expect that LIKE ‘%%’ will be able to use that index - it won’t.
You don’t achieve much by indexing nearly the whole table.
The goal of the index is that RDBMS is scanning smaller file (index file) in order to obtain where the record is in the larger file (data file) without having to scan the larger file. That’s why indexes speed up certain queries.
If you have an index on column named name, and if that column contains record “Lorem Ipsum Dolor Sit Amet” and if you query for
WHERE `name` LIKE '%Dolor%'
the index will not be used. Why you might wonder - because the database HAS to scan everything containing word ‘Dolor’ so it cannot use the index properly.
If you were to use
WHERE `name` = 'Lorem Ipsum Dolor Sit Amet'
There are certain tricks on how to use indexes for char/varchar columns but in order not to overcomplicate things - it’d be better if you could explain what you want to achieve and I’m sure there’s a better approach hiding somewhere 
Ah sorry. din’t realize I posted wrong table
create table instituteInfo (
id int auto_increment primary key,
name varchar(25) not null,
email varchar(255) not null,
idType int references instituteDetailType(id),
idInstitute int,
isActive tinyint not null default 1,
index(name,idInstitute)
);
As for the end result, I want user to select a certain alphabet and then show ALL institutes “starting” with that alphabet. If no alphabet is specified then show everything.
For startes, I think i can make it NOT use like if i am showing all records.
But as you explained, i think there might not be anything possible in this case as I have to use like (unless you can share you “hidden” methods 
)
How many records do you think you will have in total? These optimizations aren’t that necessary if we’re not talking about high concurrency, many users and few hundred thousands of records. If you think you’ll have something like 10k records, I think you should be ok.
Tweaking your innodb settings will probably yield better results too (look up innodb_buffer_pool_size), and seeing the table is small anyway - I see no need in tampering with indexes to try to speed up LIKE ‘%%’ searches.
Thanks. I don’t think I’ll have more than 10,000 records. So, I guess I’ll be safe in that regards. I won’t have access to settings file (Shared Server) so can’t change that buffer_pool_size but its good to know.
thanks