MySQL index reference - which is faster?
What I mean:
I have a project that uses ActiveCollab 2 and the database structure is new to me - almost everything is stored in a project_objects
table and has a recursive hierarchical relationship:
- Entry 1234 could be
type
"Ticket" sparent_id
of 123 - Entry 123 can be
type
"Category"parent_id
from 12 - Record 12 could be
type
"Milestone", etc.
Currently, there are over 450,000 records in this table, and in many queries the code reference is a field name
that does not have an index. The approximate value can be Design
or Development
.
This could be an example request:
SELECT * FROM project_objects WHERE type = "Ticket" and name = "Design"
My problem:
I have a query that takes 12-15 seconds and I can feel it from this
name
where the index is missing and a full text search is required. My understanding of indexes is that if I add it to a field name
it will speed up reads but slow down inserts and updates. Do I need to index rebuild completely every time a record is added or updated or is it just changed / added? I don't want to optimize this query with an index if it means drastically slowing down other parts of the codebase that depend on faster writes.
My question is:
Assuming 100 views and 100 records per day, which is likely to be a faster process for MySQL - running the above query on the above table without an index, or having to rebuild the index every time a record is added?
I do not have the knowledge or authority to run tests, but I would like to offer a proposal to a client who does not sound completely newbie. Thank!
EDIT : Here is the table:
'CREATE TABLE `project_objects` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`source` varchar(50) DEFAULT NULL,
`type` varchar(30) NOT NULL DEFAULT ''ProjectObject'',
`module` varchar(30) NOT NULL DEFAULT ''system'',
`project_id` int(10) unsigned NOT NULL DEFAULT ''0'',
`milestone_id` int(10) unsigned DEFAULT NULL,
`parent_id` int(10) unsigned DEFAULT NULL,
`parent_type` varchar(30) DEFAULT NULL,
`name` varchar(150) DEFAULT NULL,
`body` longtext,
`tags` text,
`state` tinyint(4) NOT NULL DEFAULT ''0'',
`visibility` tinyint(4) NOT NULL DEFAULT ''0'',
`priority` tinyint(4) DEFAULT NULL,
`created_on` datetime DEFAULT NULL,
`created_by_id` smallint(5) unsigned NOT NULL DEFAULT ''0'',
`created_by_name` varchar(100) DEFAULT NULL,
`created_by_email` varchar(100) DEFAULT NULL,
`updated_on` datetime DEFAULT NULL,
`updated_by_id` smallint(5) unsigned DEFAULT NULL,
`updated_by_name` varchar(100) DEFAULT NULL,
`updated_by_email` varchar(100) DEFAULT NULL,
`due_on` date DEFAULT NULL,
`completed_on` datetime DEFAULT NULL,
`completed_by_id` smallint(5) unsigned DEFAULT NULL,
`completed_by_name` varchar(100) DEFAULT NULL,
`completed_by_email` varchar(100) DEFAULT NULL,
`comments_count` smallint(5) unsigned DEFAULT NULL,
`has_time` tinyint(1) unsigned NOT NULL DEFAULT ''0'',
`is_locked` tinyint(3) unsigned DEFAULT NULL,
`estimate` float(9,2) DEFAULT NULL,
`start_on` date DEFAULT NULL,
`start_on_text` varchar(50) DEFAULT NULL,
`due_on_text` varchar(50) DEFAULT NULL,
`workflow_status` int(4) DEFAULT NULL,
`varchar_field_1` varchar(255) DEFAULT NULL,
`varchar_field_2` varchar(255) DEFAULT NULL,
`integer_field_1` int(11) DEFAULT NULL,
`integer_field_2` int(11) DEFAULT NULL,
`float_field_1` double(10,2) DEFAULT NULL,
`float_field_2` double(10,2) DEFAULT NULL,
`text_field_1` longtext,
`text_field_2` longtext,
`date_field_1` date DEFAULT NULL,
`date_field_2` date DEFAULT NULL,
`datetime_field_1` datetime DEFAULT NULL,
`datetime_field_2` datetime DEFAULT NULL,
`boolean_field_1` tinyint(1) unsigned DEFAULT NULL,
`boolean_field_2` tinyint(1) unsigned DEFAULT NULL,
`position` int(10) unsigned DEFAULT NULL,
`version` int(10) unsigned NOT NULL DEFAULT ''0'',
PRIMARY KEY (`id`),
KEY `type` (`type`),
KEY `module` (`module`),
KEY `project_id` (`project_id`),
KEY `parent_id` (`parent_id`),
KEY `created_on` (`created_on`),
KEY `due_on` (`due_on`)
KEY `milestone_id` (`milestone_id`)
) ENGINE=InnoDB AUTO_INCREMENT=993109 DEFAULT CHARSET=utf8'
source to share
As @Ray points out, indexes do not need to be rebuilt on every insert, update or delete operation. So, if you want to improve the performance of these (or similar) queries, add either an index on (name, type)
or on (type, name)
.
Since you already have an index only for (type)
, I would add the first one:
ALTER TABLE project_objects
ADD INDEX name_type_IDX
(name, type) ;
This can take a few seconds on a busy server, but it needs to be done once, and then all requests with conditions like yours will be useful. It can also improve the performance of several other types of queries, which include only name
or name
and type
:
WHERE name = 'Design' AND type = 'Ticket' --- your query
WHERE name = 'Design' --- condition on `name` only
GROUP BY name --- group by `name`
WHERE name LIKE 'Design%' --- range condition on `name` only
WHERE name = 'Design' --- equality condition on `name`
AND type LIKE 'Ticket%' --- and range condition on `type`
WHERE name = 'Design' --- equality condition on `name`
GROUP BY type --- and group by `type`
GROUP BY name --- group by `name`
, type --- and `type`
source to share
Inserting the cost of adding an index to one point in a column name
is most likely negligible β it will likely add a constant increase in time, perhaps no more than a few milliseconds. You will eat up additional disk space, but this is usually not a concern. Nothing like the few seconds you experience when choosing performance.
Add an index, enjoy the performance improvement.
BTW: Indexes are not rebuilt per insert. They are usually done in B-Trees and if you delete often should require very little rebalancing once you get more than a few levels (and rebalancing with shallow depth is pretty cheap).
source to share