DTP


 
Lively discussions on the graphic arts and publishing — in print or on the web


Go Back   Desktop Publishing Forum > General Discussions > Web Site Building & Maintenance

Reply
 
Thread Tools Display Modes
Old 05-30-2006, 12:02 AM   #1
iamback
Member
 
iamback's Avatar
 
Join Date: Oct 2005
Location: Amsterdam, NL
Posts: 4,894
Default Weird MySQL problem

I'm running into a problem with MySQL that has me stumped. I cannot find anything about this behavior on the MySQL site either.

I have a table defined with one (string) column, and a PRIMARY key on that column (so each value must also be unique). Now assume I have the following records:
  • bbbbb
  • eeeee
  • pfffft

Next, I change one value, let's say I change "eeeee" to "zzzzz". If I subsequently do a SELECT without an ORDER BY clause, what I would expect is this:
  • bbbbb
  • zzzzz
  • pfffft
Instead, what I get is this:
  • bbbbb
  • pfffft
  • zzzzz

It seems MySQL implicitly sorts by the (primary) key, even if this is not specified with an ORDER BY clause. I cannot find a specification of this behavior.

Unfortunately - for a maintenance form of the table - I actually need to retrieve the records in the same order after a modification, regardless of what strings the column contains. The only workaround I can think of is to add an (otherwise redundant) auto_increment primary key, and define a second key on the actual data column as UNIQUE. This will work, but I'm not fond of adding redundant data...

Any other ideas, anyone?

   
__________________
Marjolein Katsma
Look through my eyes on Cultural Surfaces (soon!), My ArtFlakes shop and Flickr.
Occasionally I am also connecting online dots... and sometimes you can follow me on Marjolein's Travel Blog
iamback is offline   Reply With Quote
Old 05-30-2006, 12:19 AM   #2
iamback
Member
 
iamback's Avatar
 
Join Date: Oct 2005
Location: Amsterdam, NL
Posts: 4,894
Default Illustration

A screenshot to illustrate what happens in practice now: I changed a record from "zzzzzzzzz" (sorted at the end) to "aaaaaaaa". In the result, the check mark for a successful update is supposed to be next to the actually changed record - but the implcit reordering makes a joke of this.

Since the form allows for applying multiple records updates at a time, this can get a lot worse...
Attached Thumbnails
Click image for larger version

Name:	hsnap202.png
Views:	110
Size:	10.8 KB
ID:	573  

   
__________________
Marjolein Katsma
Look through my eyes on Cultural Surfaces (soon!), My ArtFlakes shop and Flickr.
Occasionally I am also connecting online dots... and sometimes you can follow me on Marjolein's Travel Blog
iamback is offline   Reply With Quote
Old 05-30-2006, 01:03 AM   #3
Kelvyn
Staff
 
Kelvyn's Avatar
 
Join Date: Feb 2005
Location: In the Heart of the English Lake District
Posts: 1,381
Default

I have not seen that before, but I think I have always used ORDER BY. This is strange, as the default "list" is obviously sorted alphabetically. I'd be tempted just to add a secondary key.

   
__________________
Kelvyn

Web site design, hosting and marketing, Keswick in the UK Lake District

If you are planning a visit to Keswick then try Keswick Tourist Information website

Kelvyn is offline   Reply With Quote
Old 05-30-2006, 01:33 AM   #4
iamback
Member
 
iamback's Avatar
 
Join Date: Oct 2005
Location: Amsterdam, NL
Posts: 4,894
Default

Quote:
Originally Posted by Kelvyn
I have not seen that before, but I think I have always used ORDER BY. This is strange, as the default "list" is obviously sorted alphabetically. I'd be tempted just to add a secondary key.
Originally I had an ORDER BY clause (because indeed I habitually use that), but found that as the records in the form simply had a generated numerical index, this would misfire when I tried to match a result (checkmark for success, cross for failure, with a popup message on hover) with the original records. Well, oops, slight design error - because that really would be expected.

My next idea was to generate the query, and add an ORDER BY clause only if I have a UNIQUE key in addition to an AUTO_INCREMENT PRIMARY key (in which case I could use the primary as record index on the form to tie message to record). Setting up for generating the query was no big problem, and without an auto-increment primary key the query was correctly generated without the ORDER BY clause.

And then I was surprised that the records were sorted anyway... It seems there's some sort of optimization going on behind the scene (MySQL using the index only? already sorted?)

It's all part of an effort to automatically generate forms for an "admin" area of the site, with forms, validation and queries all generated from information in the database itself rather than "hard-coded" in the PHP code. I cracked a load of problems over the past week working on this little project, making steady -though slow- progress (it is rather complicated!). But this problem is one that I cannot "think myself out of" except by adding a key just to make such "automated" maintenance possible.

I'll do it if I need to. But I prefer to avoid redundant keys.
And anyway, it somewhat invalidates my "automatic generation" of the forms and underlying queries etc. if I always need an auto_inc primary key, rather than being able to generate code depending on whether there is one or not... (Well, I can generate the code - it just doesn't behave as expected. )

   
__________________
Marjolein Katsma
Look through my eyes on Cultural Surfaces (soon!), My ArtFlakes shop and Flickr.
Occasionally I am also connecting online dots... and sometimes you can follow me on Marjolein's Travel Blog
iamback is offline   Reply With Quote
Old 05-30-2006, 04:11 AM   #5
iamback
Member
 
iamback's Avatar
 
Join Date: Oct 2005
Location: Amsterdam, NL
Posts: 4,894
Default

Quote:
Originally Posted by iamback
It seems there's some sort of optimization going on behind the scene (MySQL using the index only? already sorted?)
Confirmed. I used an EXPLAIN to figure out what MySQL is actually doing with my simple query:
Code:
SELECT *
FROM a_pagetype
which (displayed as an array) results in:
Code:
Array
(
    [0] => Array
        (
            [table] => a_pagetype
            [type] => index
            [possible_keys] => 
            [key] => PRIMARY
            [key_len] => 25
            [ref] => 
            [rows] => 8
            [Extra] => Using index
        )

)
The "Extra" column in the EXPLAIN result says "Using index" - of which MySQL docs say:
Quote:
Using index

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.
The "This strategy can be used..." bit is somewhat misleading as this whole page (and others about optimization) describes what MySQL is doing, and how it decides what to do. Inspecting the SELECT syntax, I see no way to tell MySQL to not use this optimization.

So now I have two options (unless there are more that I cannot "see"):
  • add an extra (auto_inc) primary key to the table and use a UNIQUE key on the data column to enforce no duplicates (and sort when necessary but not when not); or
  • construct a "row index" for the form by concatenating all columns of a UNIQUE key (or the whole row in case there is no unique key either!)
The second option may be "costly" (processing-wise) but this is just for an admin interface, not for front-end data maintenance; so I'll try that next. At least that strategy should allow me to use tables as-is, and not be forced to add a redundant index for maintenance (or being able to do flexible maintenance only on tables that have an auto_inc primary key).

   
__________________
Marjolein Katsma
Look through my eyes on Cultural Surfaces (soon!), My ArtFlakes shop and Flickr.
Occasionally I am also connecting online dots... and sometimes you can follow me on Marjolein's Travel Blog
iamback is offline   Reply With Quote
Old 05-30-2006, 05:18 AM   #6
Barrie Greed
Member
 
Join Date: May 2006
Location: Stringston, Somerset,UK
Posts: 111
Default

Try
SELECT *
FROM a_pagetype ignore index(primary)
Barrie Greed is offline   Reply With Quote
Old 05-30-2006, 05:22 AM   #7
Daudio
Member
 
Daudio's Avatar
 
Join Date: Aug 2005
Location: Royal Oak, Detroit suburb
Posts: 174
Default

Marjolein,

Could there be a mySQL word that would allow you to:

ORDER BY Natural_Row_Order

I don'r know if 'Natural_Row_Order' (or whatever it may be) exists, but that would be a much cleaner approach to your problem.

   
__________________
Dave

DaveAyers.com, Brass Backshop Forum
Daudio is offline   Reply With Quote
Old 05-30-2006, 06:20 AM   #8
iamback
Member
 
iamback's Avatar
 
Join Date: Oct 2005
Location: Amsterdam, NL
Posts: 4,894
Default

Quote:
Originally Posted by Barrie Greed
Try
SELECT *
FROM a_pagetype ignore index(primary)
Maybe there is an "IGNORE" clause in MySQL 5.0 or higher - but what I have on the server is4.0, and the documentation for SELECT for that version (and 4.1) mentions no such clause (or anything that suggests the capability). Actually, I checked the pages for version 5.0 and 5.1 but they mention no such clause in thr SELECT syntax either. (Maybe another database can do this? Oracle, maybe? I worked with that a little but it's a long time ago - I may be vaguely remembering such a capability though.)

I wish...

I know I can get it to work by generating a query to add a "form row index" (rather than adding a key to the table) - in fact I'm halfway there by now - but it's not pretty. (But at least all the ugliness will get buried deep in my database access layer.)

I am now (when I tell the function to generate a "form index" and which columns to use for that) generating queries like this:
Code:
SELECT CONCAT(`pagetype`,`block_id`,`parent_id`) AS index_f, `pagetype`,`block_id`,`parent_id`,`seq`,`active`,`params`
FROM a_page_block
ORDER BY `pagetype`,`block_id`,`parent_id`
(where I have a primary key consisting of columns pagetype,block_id and parent_id). That works... now I need to massage my form-generating code to make proper use of that extra column. (Sigh)

   
__________________
Marjolein Katsma
Look through my eyes on Cultural Surfaces (soon!), My ArtFlakes shop and Flickr.
Occasionally I am also connecting online dots... and sometimes you can follow me on Marjolein's Travel Blog

Last edited by iamback; 05-30-2006 at 07:32 AM. Reason: I actually have 4.0 on the server...
iamback is offline   Reply With Quote
Old 05-30-2006, 06:26 AM   #9
iamback
Member
 
iamback's Avatar
 
Join Date: Oct 2005
Location: Amsterdam, NL
Posts: 4,894
Default

Quote:
Originally Posted by Daudio
Could there be a mySQL word that would allow you to:

ORDER BY Natural_Row_Order

I don't know if 'Natural_Row_Order' (or whatever it may be) exists, but that would be a much cleaner approach to your problem.
See my reply to Barrie - you describe exactly the type of feature I'm looking for, but it doesn't seem MySQL has such a thing (not even in version 5.1 - which I can't use anyway).

   
__________________
Marjolein Katsma
Look through my eyes on Cultural Surfaces (soon!), My ArtFlakes shop and Flickr.
Occasionally I am also connecting online dots... and sometimes you can follow me on Marjolein's Travel Blog
iamback is offline   Reply With Quote
Old 05-30-2006, 07:05 AM   #10
Daudio
Member
 
Daudio's Avatar
 
Join Date: Aug 2005
Location: Royal Oak, Detroit suburb
Posts: 174
Default

M,

Quote:
but it doesn't seem MySQL has such a thing (not even in version 5.1 - which I can't use anyway)
Have you looked at the HANDLER syntax ? Otherwise I couldn't find anything else...

   
__________________
Dave

DaveAyers.com, Brass Backshop Forum
Daudio is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Weird hardware/router problem? LoisWakeman Web Site Building & Maintenance 14 03-16-2007 10:15 AM
PHP5x -> MySQL 5 Stored Procedure Problem LenHewitt Web Site Building & Maintenance 2 12-18-2006 03:07 PM
weird CSS problem in IE6 dacoyle Web Site Building & Maintenance 12 01-26-2006 06:18 PM
Help! PHP/MySQL problem annc Web Site Building & Maintenance 14 07-15-2005 12:09 PM


All times are GMT -8. The time now is 08:43 PM.


Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Contents copyright 2004–2014 Desktop Publishing Forum and its members.