Mysql – Database and query optimizacion

MySQLmysql-5.5optimizationperformancequery-performance

I have a database containing three tables: tbl_database (main table is the primary), tbl_cmdatabase and tbl_blacklist. The three tables share the same structure but differ in the number of records that can be. This is the SQL structure, that basically is the same:

CREATE TABLE IF NOT EXISTS `tbl_ndatabase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_telefonica` int(11) NOT NULL,
  `number` varchar(11) COLLATE utf8_spanish2_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci AUTO_INCREMENT=1 ;

Table tbl_database contains ~194 074 records and they should continue to grow in large numbers, table tbl_cmdatabase contains ~45,742 records and similarly can increase but perhaps not so exaggerated as tbl_database but would increase and finally the table tbl_blacklist having ~92,038 records. I am developing an application where I need to verify that the number that I will insert into table tbl_database is not in tables tbl_blacklist and tbl_cmdatabase so I need to check for every row looking for the number (this queries are slow in this amount of records). What I need to do is to optimize some tables or change the structure or not because when I perform these queries the server tends to fall as the shared hosting does not support large queries, anyone can help me with this issue? Any suggestions?

Edit: Added a file for test data

Best Answer

Further to the earlier discussion and the answer offered by @Frustrated, here is what I came up with for a Stored Procedure:

CREATE PROCEDURE insertNumber(IN newNum VARCHAR(11))
BEGIN
    DECLARE i int;
    SELECT COUNT(*) INTO i
        FROM
        (
            SELECT `number` FROM `tbl_blacklist` WHERE `number`=newNum
            UNION
            SELECT `number` FROM `tbl_cmdatabase`  WHERE `number`=newNum
        ) AS t1;
    IF i = 0 THEN
        INSERT INTO `tbl_database` (`number`) VALUES (newNum);
    END IF;
END;

I tested it under MySQL 5.5.29 by issuing commands like CALL insertNumber('123-1234'); and it seemed to work just fine.

Or, perhaps it might be more convenient to put that same logic in an INSERT trigger instead:

CREATE TRIGGER checkLists 
BEFORE INSERT ON `tbl_database`
FOR EACH ROW
BEGIN
    DECLARE i int;
    SELECT COUNT(*) INTO i
        FROM
        (
            SELECT bl.`number` FROM `tbl_blacklist` bl
                WHERE bl.`number`=new.`number`
            UNION
            SELECT cm.`number` FROM `tbl_cmdatabase` cm
                WHERE cm.`number`=new.`number`
        ) AS t1;
    IF i > 0 THEN
        SIGNAL SQLSTATE '45000';  # unhandled user-defined exception
    END IF;
END;

Obviously, the number column should be indexed in both the tbl_blacklist and tbl_cmdatabase tables.

Related Question