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_telefonica` int(11) NOT NULL,
  `number` varchar(11) COLLATE utf8_spanish2_ci NOT NULL,
  PRIMARY KEY (`id`)

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

  • Could you post the query that you're currently using, and also the query plan? – FrustratedWithFormsDesigner Mar 20 '13 at 14:26
  • 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 – ReynierPM Mar 20 '13 at 14:53
  • You can read about query plans here: dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html – FrustratedWithFormsDesigner Mar 20 '13 at 14:58
  • 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. – ypercubeᵀᴹ Mar 20 '13 at 15:04
  • @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 – ReynierPM Mar 20 '13 at 15:09

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))
    DECLARE i int;
            SELECT `number` FROM `tbl_blacklist` WHERE `number`=newNum
            SELECT `number` FROM `tbl_cmdatabase`  WHERE `number`=newNum
        ) AS t1;
    IF i = 0 THEN
        INSERT INTO `tbl_database` (`number`) VALUES (newNum);
    END IF;

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:

BEFORE INSERT ON `tbl_database`
    DECLARE i int;
            SELECT bl.`number` FROM `tbl_blacklist` bl
                WHERE bl.`number`=new.`number`
            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;

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

  • 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 Mar 21 '13 at 2:36
  • 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? – ReynierPM Mar 21 '13 at 2:37
  • 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. – Gord Thompson Mar 21 '13 at 9:10

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(*)
  SELECT number FROM tbl_blacklist
  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:


  • 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 – ReynierPM Mar 20 '13 at 16:09
  • 1
    Do you really think that counting over a cross join of 2 tables is going to be of any help? – ypercubeᵀᴹ Mar 20 '13 at 16:09
  • @ypercube: Hmm I suppose it's not the best way to do it. – FrustratedWithFormsDesigner Mar 20 '13 at 16:20
  • @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. – FrustratedWithFormsDesigner Mar 20 '13 at 16:23
  • @FrustratedWithFormsDesigner I'm not a store procedure expert also not a MySQL expert so I'll need some help on this – ReynierPM Mar 20 '13 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.