Help please.......need suggestions

Hi i'm new in this group and i need some suggestions for a very big
problem
I had two tables with the same structure and they contains both
60.000 / 100.000 records . don't ask me why i had two tables with the
same structure because i'm forced to do it this way. The problem is
that when i make this query MYSQL get blocked.....it doesn't go
slowly....it really get blocked....i had to remove the process but i
need to do this query

the Query is the following : SELECT modello FROM archivio_articoli1
WHERE modello NOT IN (SELECT modello FROM archivio_articoli2)

Tables structure is the following:

CREATE TABLE `archivio_articoli1` ( //
archivio_articoli2 has the same structure
`id_articolo` double NOT NULL auto_increment,
`casa_produzione` varchar(255) NOT NULL default '',
`marca` varchar(255) NOT NULL default '',
`modello` varchar(255) NOT NULL default '',
`calibro` varchar(10) NOT NULL default '',
`ponte` varchar(10) NOT NULL default '',
`colore` varchar(20) NOT NULL default '',
`aste` varchar(10) NOT NULL default '',
`vuoto1` varchar(5) NOT NULL default '',
`vuoto2` varchar(5) NOT NULL default '',
`tipologia` set('V','S') NOT NULL default '',
`importa` set('S','') default NULL,
`prezzo_listino` decimal(10,2) NOT NULL default '0.00',
`prezzo_ingrosso` decimal(10,2) default NULL,
PRIMARY KEY (`id_articolo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=457406 ;

On Jan 12, 11:58 am, Michele Di Benedetto <mikyd...@gmail.com> wrote:
> Hi i'm new in this group and i need some suggestions for a very big
> problem
> I had two tables with the same structure and they contains both
> 60.000 / 100.000 records . don't ask me why i had two tables with the
> same structure because i'm forced to do it this way. The problem is
> that when i make this query MYSQL get blocked.....it doesn't go
> slowly....it really get blocked....i had to remove the process but i
> need to do this query
>
> the Query is the following : SELECT modello FROM archivio_articoli1
> WHERE modello NOT IN (SELECT modello FROM archivio_articoli2)
>
> Tables structure is the following:
>
> CREATE TABLE `archivio_articoli1` ( //
> archivio_articoli2 has the same structure
> `id_articolo` double NOT NULL auto_increment,
> `casa_produzione` varchar(255) NOT NULL default '',
> `marca` varchar(255) NOT NULL default '',
> `modello` varchar(255) NOT NULL default '',
> `calibro` varchar(10) NOT NULL default '',
> `ponte` varchar(10) NOT NULL default '',
> `colore` varchar(20) NOT NULL default '',
> `aste` varchar(10) NOT NULL default '',
> `vuoto1` varchar(5) NOT NULL default '',
> `vuoto2` varchar(5) NOT NULL default '',
> `tipologia` set('V','S') NOT NULL default '',
> `importa` set('S','') default NULL,
> `prezzo_listino` decimal(10,2) NOT NULL default '0.00',
> `prezzo_ingrosso` decimal(10,2) default NULL,
> PRIMARY KEY (`id_articolo`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=457406 ;

Try this syntax instead:

SELECT t1.*
FROM table1 t1
LEFT JOIN table2 t2 ON t2.id = t1.id
WHERE t2.id IS NULL;

On 12 Gen, 13:15, strawberry <zac.ca...@gmail.com> wrote:
> On Jan 12, 11:58 am, Michele Di Benedetto <mikyd...@gmail.com> wrote:
>
>
>
> > Hi i'm new in this group and i need some suggestions for a very big
> > problem
> > I had two tables with the same structure and they contains both
> > 60.000 / 100.000 records . don't ask me why i had two tables with the
> > same structure because i'm forced to do it this way. The problem is
> > that when i make this query MYSQL get blocked.....it doesn't go
> > slowly....it really get blocked....i had to remove the process but i
> > need to do this query
>
> > the Query is the following : SELECT modello FROM archivio_articoli1
> > WHERE modello NOT IN (SELECT modello FROM archivio_articoli2)
>
> > Tables structure is the following:
>
> > CREATE TABLE `archivio_articoli1` ( //
> > archivio_articoli2 has the same structure
> > `id_articolo` double NOT NULL auto_increment,
> > `casa_produzione` varchar(255) NOT NULL default '',
> > `marca` varchar(255) NOT NULL default '',
> > `modello` varchar(255) NOT NULL default '',
> > `calibro` varchar(10) NOT NULL default '',
> > `ponte` varchar(10) NOT NULL default '',
> > `colore` varchar(20) NOT NULL default '',
> > `aste` varchar(10) NOT NULL default '',
> > `vuoto1` varchar(5) NOT NULL default '',
> > `vuoto2` varchar(5) NOT NULL default '',
> > `tipologia` set('V','S') NOT NULL default '',
> > `importa` set('S','') default NULL,
> > `prezzo_listino` decimal(10,2) NOT NULL default '0.00',
> > `prezzo_ingrosso` decimal(10,2) default NULL,
> > PRIMARY KEY (`id_articolo`)
> > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=457406 ;
>
> Try this syntax instead:
>
> SELECT t1.*
> FROM table1 t1
> LEFT JOIN table2 t2 ON t2.id = t1.id
> WHERE t2.id IS NULL;


ok .....i optimized the query but i needed to add an index to the
column 'modello' to make it work very fine...................thanks a
lot for your support!