Database and query optimizacion

Question

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

• FrustratedWithFormsDesigner: Could you post the query that you're currently using, and also the query plan?
• ReynierPM: Just a SELECT * FROM tbl_database LIMIT 3000, 6000 and this is the result > Showing rows 3000 - 8999 ( 6,000 total, Query took 0.0080 sec) what is query plan? I didn't know that
• FrustratedWithFormsDesigner: You can read about query plans here: dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html
• ypercubeᵀᴹ: What is the point of going through 9000 rows in the tables? If, as you say, you only need to lookup whether a specific number is in one or two other tables, there is no need to fetch 6000 rows.You can search with simple NOT IN or NOT EXISTS subqueries.
• ReynierPM: @ypercube I check if number exists trough PHP, reading all the rows of each table and then comparing but this is veryyyyyyyy slowly and I'm trying to find a solution based on MySQL or any way to optimizing the queries or the design and I leave the SELECT statement because I need to list the records based on a number that users enter in my application that should be between 500 and 3000

Answer 1

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.

• ReynierPM: Hi @gord-thompson, I try to get how PROCEDURE and TRIGGER works but can't get the logic behind them. Also I added a test data file (first post edited) for testing purpose with the three tables I mention before. Maybe you don't understand the problem or maybe I don't understand the procedure. As I said in first post I have three identical tables: tbl_blacklist, tbl_cmdatabase and tbl_database, then I get some data from a Excel file grammatically. What I need to do is for each data in Excel check if exists in tbl_blacklist or in tbl_cmdatabase, if exists then go to the next Excel record
• ReynierPM: If not then insert in tbl_ndatabase, is that what your procedure and trigger does? Do I need to index or make unique the field numbers in each table in order to not repeat them?
• Gord Thompson: Your question states: "I need to verify that the number that I will insert into table tbl_database is not in tables tbl_blacklist and tbl_cmdatabase". The Stored Procedure and the INSERT trigger are just two different ways of accomplishing that in MySQL; you could use either one. The logic prevents a number from being added to tbl_database if it already exists in either of the other two tables. If the number does exist in either table then the INSERT into tbl_database simply does not take place.

Answer 2

I need to verify that the number that I will insert into table tbl_database is not in tables tbl_blacklist and tbl_cmdatabase

Perhaps you can do this with something like

SELECT count(*)
FROM (
 SELECT number FROM tbl_blacklist
 UNION
 SELECT number FROM tbl_cmdatabase ) AS Q
where Q.number = '$number'

When the count returned is 0, it means that $number is not in the tables. It's possible this is not the best query for this job, but I think it will work OK.

You can play around with it here:

http://sqlfiddle.com/#!2/7d697/23

• ReynierPM: Any way to use this answer to insert values without PHP intervention? If a return a count(*) then I will need to check trough PHP and then make the insert but I would like to know if any of the sentences on the post I leave could be used
• ypercubeᵀᴹ: Do you really think that counting over a cross join of 2 tables is going to be of any help?
• FrustratedWithFormsDesigner: @ypercube: Hmm I suppose it's not the best way to do it.
• FrustratedWithFormsDesigner: @ReynierPM: Not sure, I've never used that feature in MySQL. If you want to avoid any PHP, you might want to call a stored procedure (instead of directly calling INSERT) which first checks for the number being in other tables, and then inserts if it is not.
• ReynierPM: @FrustratedWithFormsDesigner I'm not a store procedure expert also not a MySQL expert so I'll need some help on this