Sorry your browser is not supported!

You are using an outdated browser that does not support modern web technologies, in order to use this site please update to a new browser.

Browsers supported include Chrome, FireFox, Safari, Opera, Internet Explorer 10+ or Microsoft Edge.

Geek Culture / Advice on MySql Query Tool

Author
Message
BatVink
Moderator
21
Years of Service
User Offline
Joined: 4th Apr 2003
Location: Gods own County, UK
Posted: 29th Sep 2004 18:54
Link
Anyone got a good MySql query tool for a local database?

PHPmyAdmin can't cope with the size of my query.

MySQL Query Browser just doesn't want to play.

BatVink
http://biglaugh.co.uk/catalog AMD 3000+ Barton, 512Mb Ram, 120 Gig Drive space, GeForce 5200 FX 128 Mb, Asus A7N8X Mobo.
Terms & Conditions apply
Back to top
Profile PM Email
Neofish
20
Years of Service
User Offline
Joined: 7th Apr 2004
Location: A swimming pool of coke
Posted: 29th Sep 2004 18:56
Link
what is the query? why not write it yourself?

:: Pulse Board Forums :: Want Gmail? Email me ::
Back to top
Profile PM Email Website
Karlos
21
Years of Service
User Offline
Joined: 18th Nov 2002
Location: United Kingdom
Posted: 29th Sep 2004 20:02
Link
Pop in an 'explain' before the query to make sure you're accessing in the most efficient way there may be some join you could put in to improve things.

You could also play around with the limit directive to reduce the number of results.

All Hail the Glory of the HypnoToad
XP Pro - Radeon 9000 Mobility- P4 3.0ish
Football management - Football Manager
Back to top
Profile PM Website
Richard Davey
Retired Moderator
22
Years of Service
User Offline
Joined: 30th Apr 2002
Location: On the Jupiter Probe
Posted: 29th Sep 2004 20:23
Link
Err I think he's actually written the Query already guys, he just wants a good GUI to run it through. The MySQL command-line one (in Windows) can't handle queries over a certain (very small) length for example.

BatVink - I use two programs depending on my mood, MySQL Front Version 2.2 (do NOT look at the newer version 3 and above, they are terrible in comparison).

I also recently bought SQLyog 3.7 which is great and much faster than MySQL Front (but, not free). Has very good export options too.

For actual complex query or table design I use the excellent, and free, DB Designer 4.

Cheers,

Rich

"I am not young enough to know everything."
- Oscar Wilde
Back to top
Profile PM Email Website
BatVink
Moderator
21
Years of Service
User Offline
Joined: 4th Apr 2003
Location: Gods own County, UK
Posted: 29th Sep 2004 20:44 Edited at: 29th Sep 2004 20:44
Link
Cheers all.

Yes, I already have the query but like Rich says, finding a tool powerful enough to run it is a problem. This one isn't complex, it's just manipulating a lot of data, and is in itself a very long series of statements. Optimisation isn't the issue.

I'll check out the tools you mentioned. DB Designer sounds interesting too.

While we're on the topic, can anyone translate this problem into SQL.

I have one table, products and categories with a many to many relationship. I want to return one row per product, and it must also return one of the categories too. I don't care which category, but it must be in the result set.

SELECT DISTINCT won't do it, if a product has 3 categories it will return 3 rows because category is in the collection of fields.

SELECT TOP * wont do it, it works on the full resultset, not an a row basis.

But maybe there is a way to combine the 2, reduce through a join, or some damn thing that means I don't have to script the results!

BatVink
http://biglaugh.co.uk/catalog AMD 3000+ Barton, 512Mb Ram, 120 Gig Drive space, GeForce 5200 FX 128 Mb, Asus A7N8X Mobo.
Terms & Conditions apply
Back to top
Profile PM Email
adr
21
Years of Service
User Offline
Joined: 21st May 2003
Location: Job Centre
Posted: 29th Sep 2004 21:27
Link
Some of the plebs at work here use something called "MySQL control center" on their windows boxen, if that helps


I think the taxis are bulletproof
Back to top
Profile PM Website
Richard Davey
Retired Moderator
22
Years of Service
User Offline
Joined: 30th Apr 2002
Location: On the Jupiter Probe
Posted: 30th Sep 2004 01:46
Link
BatVink - post the table structure, I can never work off descriptions alone.

"I am not young enough to know everything."
- Oscar Wilde
Back to top
Profile PM Email Website
David T
Retired Moderator
22
Years of Service
User Offline
Joined: 27th Aug 2002
Location: England
Posted: 30th Sep 2004 02:43
Link
Are you using a LEFT JOIN? If you have two tables:



Then this query'll return a product plus it's category info:



Get 15 new commands, all the date / time commands left out of DBPro for free!
DOWNLOAD PLUGINS HERE: http://www.davidtattersall.me.uk/ and select "DarkBasic"
Back to top
Profile PM Email Website
BatVink
Moderator
21
Years of Service
User Offline
Joined: 4th Apr 2003
Location: Gods own County, UK
Posted: 30th Sep 2004 03:20 Edited at: 30th Sep 2004 03:21
Link
David, it's one table, from which I want to reduce the rows to just one per product. So I want to go from

Product1 Category1
Product1 Category2
Product1 Category4
Product2 Category3
Product2 Category4
Product2 Category6

to

Product1 Category1
Product2 Category3

but for about 300 products across 12 categories

BatVink
http://biglaugh.co.uk/catalog AMD 3000+ Barton, 512Mb Ram, 120 Gig Drive space, GeForce 5200 FX 128 Mb, Asus A7N8X Mobo.
Terms & Conditions apply
Back to top
Profile PM Email
David T
Retired Moderator
22
Years of Service
User Offline
Joined: 27th Aug 2002
Location: England
Posted: 30th Sep 2004 04:12
Link
Hmmmmm. Interesting. I'll have a think about that.

Get 15 new commands, all the date / time commands left out of DBPro for free!
DOWNLOAD PLUGINS HERE: http://www.davidtattersall.me.uk/ and select "DarkBasic"
Back to top
Profile PM Email Website
Phaelax
DBPro Master
21
Years of Service
User Offline
Joined: 16th Apr 2003
Location: Metropia
Posted: 30th Sep 2004 06:00
Link
I use SQL+
I've pasted in a whole page of sql commands and it took it all.

"eureka" - Archimedes
Back to top
Profile PM Email Website
spooky
22
Years of Service
User Offline
Joined: 30th Aug 2002
Location: United Kingdom
Posted: 30th Sep 2004 07:26
Link
Just do something like this:

SELECT Product_Id, Min(Cat_Id) AS MinCat_Id
FROM Products
GROUP BY Product_Id
ORDER BY Product_Id;

If there are more than one cat per product it will pick the lowest one. You can replace 'Min' with all sorts of things, like 'Max' to give largest cat per product, 'Count' to tell you how many cats per product.


Boo!
Back to top
Profile PM Website
BatVink
Moderator
21
Years of Service
User Offline
Joined: 4th Apr 2003
Location: Gods own County, UK
Posted: 30th Sep 2004 08:48
Link
Cheers, Spooky . Now I see it, it's obvious, and I've done it many times before. The word GROUP disappeared from my vocabulary, and that is what I needed

BatVink
http://biglaugh.co.uk/catalog AMD 3000+ Barton, 512Mb Ram, 120 Gig Drive space, GeForce 5200 FX 128 Mb, Asus A7N8X Mobo.
Terms & Conditions apply
Back to top
Profile PM Email
Back to top

Login to post a reply

Server time is: 2024-09-22 18:38:24
Your offset time is: 2024-09-22 18:38:24