Page 1 of 1
except not working in phpmyadmin
Posted: Fri Jun 25, 2010 3:24 am
by mayanktalwar1988
Code: Select all
select links FROM `crawled` except select pagelink from test
this thing above is giving sql syntax error
Code: Select all
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select pagelink from test LIMIT 0, 30' at line 1
Re: except not working in phpmyadmin
Posted: Fri Jun 25, 2010 4:41 am
by Eran
There is no 'EXCEPT' in MySQL.
Use a negative left join instead -
Code: Select all
SELECT `links` FROM `crawled` LEFT JOIN `test` ON `crawled`.`links`=`test`.`pagelink` WHERE `test`.`pagelink` IS NULL
Re: except not working in phpmyadmin
Posted: Fri Jun 25, 2010 8:07 am
by mayanktalwar1988
still not working phpmyadmin goes on loading forever and nothing happens..i used your query directly in phpmyadmin .simple query are working like getting distinct etc......what can be wrong?except,not in,exists nothing works
Re: except not working in phpmyadmin
Posted: Fri Jun 25, 2010 8:08 am
by Eran
What is the size of those tables? possibly it's taking too long to complete and timing out. You can probably improve that with proper indexing
Re: except not working in phpmyadmin
Posted: Fri Jun 25, 2010 8:14 am
by mayanktalwar1988
test is of 25000 rows and crawler is of 38000 plus rows
Re: except not working in phpmyadmin
Posted: Fri Jun 25, 2010 8:38 am
by Eran
Run EXPLAIN with the query I gave you, and post the results here
Re: except not working in phpmyadmin
Posted: Fri Jun 25, 2010 8:54 am
by mayanktalwar1988
i never used explain wil take a while for me to post the result...let me first google what explain is
Re: except not working in phpmyadmin
Posted: Fri Jun 25, 2010 9:05 am
by mayanktalwar1988
i have typed it on notepad as seen in phpmyadmin here it goes
Code: Select all
select type for crawlerebook and test is simple
type for both is all
possible_keys for both is null
key for both is null
key_len for both is null
ref for both is null
in extra column crawlerebook shows nothing(blank)
and test says using where;not exists
is this what you asking?
Re: except not working in phpmyadmin
Posted: Fri Jun 25, 2010 9:24 am
by Eran
You somehow made it very difficult by not copying the actual structure of the explain..
Anyway, you should add indexes on 'links' in the crawled table and on 'pagelink' in the test table.
Re: except not working in phpmyadmin
Posted: Fri Jun 25, 2010 9:37 am
by mayanktalwar1988
Code: Select all
<?php
include("config.php");
$sql = "explain SELECT `links` FROM `crawlerebook` LEFT JOIN `test` ON `crawlerebook`.`links`=`test`.`pagelink` WHERE `test`.`pagelink` IS NULL";
echo mysql_query($sql);
?>
with the above code al it is printing is this
Re: except not working in phpmyadmin
Posted: Fri Jun 25, 2010 9:52 am
by Eran
Please review the manual on how to use mysql_query()
http://php.net/manual/en/function.mysql-query.php