CODEWITHSUNDEEP
mysql
Matt Dawdy
Matt Dawdy

Reputation: 19707

MySQL Query not returning

I'm unfamiliar with MySQL, and I don't know where to begin with solving this query issue.

SELECT * 
FROM `rmedspa`.`clients` c  
inner join `rmedspa`.`tickets` t on c.clientid = t.clientid  
where c.fldclass is not null     
AND t.ticketID > 0

This query returns just fine in MySQL Workbench, in 30 seconds, and the IDE is limiting the query results to 1000 records. The database is not on my own machine, but on a server that is in a different location (in other words, it's going out to the internet and it's slow). If I add an order by at the end, the query never returns.

SELECT * 
FROM `rmedspa`.`clients` c  
inner join `rmedspa`.`tickets` t on c.clientid = t.clientid  
where c.fldclass is not null     
AND t.ticketID > 0
ORDER BY t.ticketid

There are "many" tickets for 1 client. t.ticketid is an int. clientid is an int, too.

I don't know where to begin to find out why the ORDER BY is causing this query to never return. It doesn't fail, it just doesn't return.

Upvotes: 0

Views: 2939

Answers (1)

Chapter2
Chapter2

Reputation: 102

This Post is a short review of previous comments plus some hints on SQL-Query for you.

Query building

  • Whenever the column names of a join condition match, you can rewrite the join statement as follows: INNER JOIN {database}.{table} t USING({joinColumn})
  • Check your indexes - in your case: Are the columns of the join condition and the where statement indexed?

mySQL result

  • The ORDER BY statement often requires a reordering of the result set in memory. If you expected large results (many rows) and/or large rows (many columns) but see no result the mySQL Server is not properly configured for that case - How MySQL Uses Memory, Optimizing Queries with EXPLAIN

Upvotes: 1

Related Questions