Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
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
    
You're right I accept the answer and made modifications later to suit what I need –  ReynierPM Mar 21 '13 at 20:41

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

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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