except not working in phpmyadmin

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mayanktalwar1988
Forum Contributor
Posts: 133
Joined: Wed Jul 08, 2009 2:44 am

except not working in phpmyadmin

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: except not working in phpmyadmin

Post 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
mayanktalwar1988
Forum Contributor
Posts: 133
Joined: Wed Jul 08, 2009 2:44 am

Re: except not working in phpmyadmin

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: except not working in phpmyadmin

Post 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
mayanktalwar1988
Forum Contributor
Posts: 133
Joined: Wed Jul 08, 2009 2:44 am

Re: except not working in phpmyadmin

Post by mayanktalwar1988 »

test is of 25000 rows and crawler is of 38000 plus rows
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: except not working in phpmyadmin

Post by Eran »

Run EXPLAIN with the query I gave you, and post the results here
mayanktalwar1988
Forum Contributor
Posts: 133
Joined: Wed Jul 08, 2009 2:44 am

Re: except not working in phpmyadmin

Post by mayanktalwar1988 »

i never used explain wil take a while for me to post the result...let me first google what explain is
mayanktalwar1988
Forum Contributor
Posts: 133
Joined: Wed Jul 08, 2009 2:44 am

Re: except not working in phpmyadmin

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: except not working in phpmyadmin

Post 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.
mayanktalwar1988
Forum Contributor
Posts: 133
Joined: Wed Jul 08, 2009 2:44 am

Re: except not working in phpmyadmin

Post 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

Code: Select all

Resource id #4
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: except not working in phpmyadmin

Post by Eran »

Please review the manual on how to use mysql_query()
http://php.net/manual/en/function.mysql-query.php
Post Reply