How to optimize a SQL query (or MySQL table) to speed up its execution?

Question

MySQL

I have this MySQL table :

CREATE TABLE IF NOT EXISTS `mails` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `to` varchar(255) NOT NULL,
  `object` varchar(30) NOT NULL,
  `data` text NOT NULL,
  `temp_id` int(11) NOT NULL,
  `mailing_id` int(11) NOT NULL,
  `priority` tinyint(1) NOT NULL,
  `send_time` datetime NOT NULL,
  `error` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

The table is filled with data, now about: 350 thousand records.
In a loop, several thousand queries (3000-10000 pieces) of the same type are executed to this table:

SELECT `id`, `to` FROM `mails` WHERE (`to` LIKE '[email protected]' OR `to` LIKE '[email protected],%' OR `to` LIKE '%,[email protected]' OR `to` LIKE '%,[email protected],%') AND `mailing_id` = 172

Question: how to optimize this SQL query (or the MySQL table itself) to increase the speed of its execution?

ps: the request is made in Yii 1.x via the query builder: Yii::app()->db->createCommand()->select("id, to")->from('{{mails}}'). ...

Answer 1

run not several thousand requests, but one. get rid of the useless cycle first. even in line zero!

Answer 2

Move addresses to a separate table and make a many-to-many relationship. Searching with LIKE if the mask starts with % is very costly and does not use indexes.

Answer 3

several thousand requests (3000-10000 pieces) of the same type

why? why? yes how so? nah horror. catastrophe. piiiiiipets. How after this phrase you can ask "what to optimize?". This is what to optimize. Remove to hell and write 1 request. The load will drop at times, and then you can earn a penny at the expense of indexes.

Answer 4

Create a composite index on the `to` and 'mailing_id` fields.
Replace WHERE clause with JOIN.
Well, and be sure to replace "several thousand requests" with one.

Answer 5

Initially, I did not add one important point: the mails table contains letters in different statuses: queued, sent, etc. My crazy cycle executing a slow query with LIKEs is inevitable with this functionality: the client has a mailing list of emails based on the address base, the address base is very large (more than 100 thousand) and the main thing is that it is used in different places of the huge system, and the worst thing: emails are written separated by commas in one field and can be repeated for different records from the mailing list, that's why I used LIKE's so as not to send more than one letter to the same Email address.
Such functionality, of course, needs to be rewritten - this is understandable, but there are a lot of dependencies, related functionality, etc. - this is all a lot of work, but a quick solution would be needed.
Within one process of generating letters, no more than 10 thousand letters are created. Therefore, I think for now the best temporary solution would be to create an intermediate table containing all email addresses for emerging letters at one address in one record in one field. Thus, there will be no need to use LIKEs, plus the table itself will contain a maximum of 3 fields and not contain more than 10 thousand records.
Thanks everyone for the comments and replies!
If you suddenly have more fresh ideas on my problem, please write here!

Answer 6

why iterate over all LIKE options? One statement LIKE
SELECT `id`, `to` FROM `mails` WHERE `to` LIKE '%[email protected]%' AND `mailing_id` = 172 is enough
is it possible to come up with a slightly different way around the loop to shorten number of iterations? little data, what does mailing_id mean? is it possible to concatenate with other values ​​and make mailing_id IN(...)?