Stop Query Ordering
select ... FROM ... WHERE ... IN ('2', '1', '7', '3', '17')
I have a query in the same format as above, where I want to select only certain items from from a table based on there ID. The problem I have is that MySQL then orders the results based on this ID (I presume this is because it is the index).
What I want to happen is the results to be returned in the same order as the query... is this possible?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Sudden Query Stop
i created this script that would loop through a csv file. the file contains about 5000 rows. now when the loop is finished, i looked at the number of rows at the database, its around 360. so i checked, i echoed errors and stuff, and when im sure no errors are left, it puzzles me that whenever the loop finishes, the row count on my database is at 360-380 rows only. i took out the query command out of the loop and echoed the SQL only..its successfull echoed all 5000 possible sql. also..i noticed that my loop actually times out after 30 seconds, resulting in 360-380 rows inserted (i am truncating the table before every loop by the way.) is there any setting in my apache/php/my.ini that i have to configure? if you need to see the code, ill post it on request..
Ordering Query
i have a query were i need to select the top 250 by a number but i want the result to be ordered alphabetically by a different column. i can't figure out how to do this because i need to order by the numerical value to select the top 250. for example: Code: SELECT the_tag, count(distinct(article_id)) as cnum FROM tags GROUP BY the_tag ORDER BY cnum desc limit 250 in the above example i get the top 250 but they are order by cnum for the purpose of selecting the top 250. how can i modify this query to still select the top 250 and also have the results ordered alphabetically by 'the_tag'?
Quesion On Ordering In A GROUPED Query
Got a question about grouping and ordering. What I am trying to do is: a) Group by a criteria by pick which result I am getting as part of that group b) Order the list of groups. Lets say I have a list of emails in the form: | id | timestamp | author | subject Now, I am trying to generate a list of the latest email subjects per author, ordered by timestamp. So I want to do: SELECT * FROM table GROUP BY author ORDER BY timestamp The problem with that, is it dosen't allow me to choose which result I am getting as part of the group, so how do I do that?
Slow Query W/ Join & Ordering
I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below: It involves two tables, products and vendors. Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description: CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `vendor_id` smallint(6) NOT NULL default Ɔ', `product_code` varchar(255) NOT NULL default '', `internal_name` varchar(255) NOT NULL default '', `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `product_code` (`product_code`), KEY `vendor_id` (`vendor_id`) ) ENGINE=MyISAM; Vendors are much more straightforward: CREATE TABLE `vendors` ( `id` smallint(6) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM; The following query executes in no MORE than 0.01 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name FROM products as p ORDER BY p.product_code ASC LIMIT 0, 30; And has the following attributes: +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name , v.name as vendor_name FROM products as p LEFT JOIN vendors as v ON v.id=p.vendor_id ORDER BY p.product_code ASC LIMIT 0, 30; It has the following characteristics: +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me. Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so: mysql> explain SELECT DISTINCT p.id -> , p.product_code -> , unix_timestamp(p.lastmodified) as lastmodified -> , p.internal_name -> , v.name as vendor_name -> FROM products as p -> LEFT JOIN vendors as v ON v.id=p.vendor_id -> LIMIT 0,30; +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,
Stop Tags
How do I change the greater than symbol from being inserted as the greater than symbol but added as > so that people cant add tags when posting for example. Also I would like to single out 1 word in my posts for example filter certain words and would like to learn how to do that.
Can't Stop Mysql
Got mysql started, created root password. Decided to change it and used mysqladmin to try and change. It says access denied. I tried to stop the service, can't do that. I even restarted the computer and it says there is still a connection on localhost:3306 and it can't make changes. I have deleted mysql and completely re-installed. Nothing changes. How can I completely stop the service that it tying up :3306. Is there anyway to see those ports and see what is connected?
Mysql Does Not Stop
Following is the error message I get when I try to stop mysql: [root@linserver root]# /etc/rc.d/init.d/mysqld stop Stopping MySQL: [FAILED] But is starts successfully [root@linserver root]# /etc/rc.d/init.d/mysqld start Starting MySQL: [ OK ] And I am unable to connect to mysql. There is no mysql.sock file in my linux system. [root@linserver root]# mysql ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
Stop Duplicate Records
How do i stop duplicate records from being inserted into the database. i need to stop duplicates of title and link from being entered .....
Start And Immediate Stop Of MySql
Installed MySQL 4.0.20, Apache 2.0.50 and php 4.3.8 on RHEL AS custom server installation. Did the following, in accordance with online forum/faq suggestions and with Julie C. Meloni's book SAMS Teach Yourself PHP, MysQL and Apache. ln -s mysql... cd mysql scripts/mysql_install_db groupadd mysql useradd -g mysql mysql chown -R root /usr/local/mysql chown -R mysql /usr/local/mysql/data chown -R mysql /usr/local/mysql chown -R root /usr/local/mysql/bin ./ysqld --user=mysql Preceding attempt to start MySql generates the following (slightly paraphrased): Can't change to run as user 'mysql' Please check that user exists. Suggestions?
How Do You Stop All The Connections To MySQL?
I don't want any application accessing my databases while performing the recovery operations. In Oracle, you can stop the TNS listener on the server side. What is the equivalent of this in MySQL?
Unespected Stop Of Mysql
I have an Italian version of EasyPHP. Mysql doesn’t start, and EasyPHP gives this message: “Mysql è stato fermato inaspettatamente” (unespected stop of Mysql), what can I do.
Stop Mysql Before A Dump?
I want to make a dump on my DB, but I was asking myself if there are any problem to do that without stopping the DB before. Have I to stop my DB before mysqldump? is there any provability that my dump backup will have any errors?
Mysql Service Will Not Stop
What does this problem mean and how can I solve it? net stop mysql The MySQL service is stopping......... (never stops) I am running Windows 2000 SP 4 with mysql 5.0.24a.
Stop Further Connections To Database
Does anyone know of a way to stop further connections to a database while letting existing ones complete? Does restarting the server allow ALL threads to complete before shutting down?
Stop Duplicate Records
Sorry if this is extremely newbie of me but how do i stop duplicate records? I have created a small site with php and mysql which allows a user to reserve a parking space online. Im only learning mysql and php so its all done very simply - I have a table with 2 fields - space and daybooked. users can go online and book for example Space number 2 for Wednesday. How would i carry out a check that would stop another user from double booking the same space?
Stop Users From Pressing
I've got a preview button in my app that runs a crystal report. For the first few seconds all is well. You can't click anything on the form and the cursor is an hourglass (as set by my code). But then the form becomes responsive again, the hourglass turns back into a pointer, and the user can select File, Close for example. So for example, the user can open an order, print it, and close the order before the crystal report code finishes printing, causing an error. I've tried this.Enabled = false; This does work but it makes all the controls greyed out. Its not that I want to prevent form events from firing as much as I want to ignore user mouse and keyboard input.
Stop Optimize Table
While trying to shorten my files, I tried optimize table - later I found that I need: mysqlcheck -u root -p[password[ --all-databases --analyze --optimize (thanks to Markus Popp). But it gives this error: mysqlcheck: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when executing 'OPTIMIZE TABLE ... ' How can I stop this? The optimise command seem to be in the way (and since I started it, my computer has got pretty slow)
How To Prevent Stop Words In A MATCH - AGAINST?
I have a simple search engine for my website, and the MATCH-AGAINST works well when search english words (contained in the `name` and `desc` columns), but when try to search a item-code contained in the `sku` column it seems like mysql handle that code (such as "hp-504") as a stop word and returns zero rows...
4.0.14 Runs CPU Usage Up And Eventually Stop
I am running 4.0.14 on a FreeBSD 4.8-STABLE machine (upgraded to CVS from last week) and I am having a problem. We run a fairly large website using PHP so there is always many connections to the database. On 4.0.12 the cpu usage never shot to over 20% and ran extremely fast. After upgrading to 4.0.14, the cpu goes anywhere between 20-60% and in many situations will not accept any database connections. After browsing the website for a few minutes, I will type mysql on the command line and it will give an error of too many connections. Does anyone know what may be causing this high load from the upgrade? Again the previous versions did not do this and ran very well. I could use 4.0.12 but I wanted to report this in case it continues in future versions.
4.0.14 Runs CPU Usage Up And Eventually Stop
I am running 4.0.14 on a FreeBSD 4.8-STABLE machine (upgraded to CVS from last week) and I am having a problem. We run a fairly large website using PHP so there is always many connections to the database. On 4.0.12 the cpu usage never shot to over 20% and ran extremely fast. After upgrading to 4.0.14, the cpu goes anywhere between 20-60% and in many situations will not accept any database connections. After browsing the website for a few minutes, I will type mysql on the command line and it will give an error of too many connections. Does anyone know what may be causing this high load from the upgrade? Again the previous versions did not do this and ran very well. I could use 4.0.12 but I wanted to report this in case it continues in future versions.
How To Get Stop Word List In Mysql
I want to use the stop word list of my sql in my program. How can I get this list in sql. Is it stored in some table in database. How can I access it using a query.
Stop Words In Mysql - Ignoring A/an/the
What are the mechanisms to make words like a/an/the in text and varchar character fields ignored when performing SELECT and utilizing ORDER BY in SQL queries? I've done some hunting around into documentation on character sets (but they are geared mainly at character-by-character rules) and saw some reference to string collating and stop words -- but not seen any of this all put together.
Create A Stop Word File
I need to change the stop word file used by the MySQL in fulltext seach. The documentation (www.mysql.com/doc/en/Fulltext_Fine-tuning.html) explains how to inform to MySQL the new stop word file through the "ft_stopword_file" variable. But I could not find any reference to the layout of this file, for example, how to separate each stop word in the list? Does someone have any experience on that?
Querying String With Full Stop (.)
SELECT * from tblprojdata WHERE projid = 'sdk4.1systest' i run the above query using an ado data connection from my web site, but it brings up an error because of the full stop. is there any way to cope with this error?
How Can I Start And Stop Mysql Server Automatically On Win 98?
I am trying to deploy an application on a Win98 machine (have no choice as this is all the customer has). My application requires the mySQL server to be running, but I cannot work out how to get win98 to automatically start the service on bootup and shut it down when it closes. Any ideas?
Ordering
I've got a few 'course' records with titles and dates. The result set should be ordered like this: title4|29.10.2007 title2|03.11.2007 title2|07.11.2007 title3|04.11.2007 title1|20.12.2008 So, the records should be grouped by title and *within* the same group they should be ordered descending by date (title2 in example). However, overall the first record should be the one with the closest date (i.e. date descending). The fact that title3 comes third, is obvious then (I think) because its date is "higher" than the closest title2 record.
Ordering A NOW() Column?
I have built a web application that allows users to input data into the database, and each entry is marked with the date and time in its seperate DB column using the NOW() mysql function inside the INSERT query. But now that it has passed new years... I've noticed that all new entires in 2007 are being placed at the bottom of the sort order when SELECT'ing all records from the DB and using ORDER BY now_col (which is the NOW() column). Why is this happening? Is NOW() not a good function to use to record the date and time of a DB record insert? What can I do to fix this or is there another solution? Add-In: The column type is DATETIME
Selecting And Ordering By AVG?
each image has it's own entry in my "images" table ie. images.images_id, images.title, images.description, images.filepath every time a user votes on an image it creates an entry into the "vote" table ie. vote.vote_id, vote.images_id, vote.vote_given (the rating given in 1-10 format) What i'd like to do at the end of the voting session is grab the images from the "images" table, in order of the average vote given to them from the "vote" table. Thanks in advance!
Changing The Ordering
Sadly, this isn't one that can be answered simply by changing ORDER BY field DESC to ORDER BY field ASC. What I need to do is to be able to display a list of items in an arbitrary order that can be changed, e.g., A B C D might need to change to C B D A I'm thinking that the best way to approach this would be to add another column sort_order that contains integer values which could then be used in an ORDER BY clause on my SELECT statements. Anyone else have any better ideas? I've done it this way before and it does work, I'm just wonder if there's some utterly brilliant solution out there that doesn't require additional metadata.
Fulltext Ordering
SELECT * FROM article WHERE MATCH(name) AGAINST('$myvar' IN BOOLEAN MODE) ORDER BY datestamp DESC LIMIT 1 The problem I am having is that it's not ordering the results by datestamp whatsoever, it's still ordering them by the fulltext score.
Ordering Of Data.
In the following query "SELECT * FROM table_name WHERE account_num>100 AND account_num<500 AND sales_num>1000 AND sales_num<5000" What order does the query return the records in? Also is there a way to set it to return all account_num that are between 100 and 500 for sales_num 1000 and then return all account_num that are between 100 and 500 for sales_num 1001 and so forth all the way up to sales_num 4999?
Ordering Values
How do i order numbers retrieved by there value, such as: 1,2,3,4,5...12,13...22 so its like that instead of 1,11,12,13,14,2,21,22,23,24,31,4,5,6...
Grouping And Ordering
SELECT q.q_id, q.user_id, IF (e.firstname IS NULL , q.name, e.firstname) AS name, t.town, e.type, e.trading_name, e.posts FROM qa_uk q LEFT JOIN ayn_experts e ON e.unique_id = q.user_id AND e.active = 1 LEFT JOIN ayn_towns t ON t.id = q.town_id WHERE q.active =1 AND t.town IS NOT NULL GROUP BY q.user_id, q.name, q.town_id ORDER BY q.datetime DESC This query runs, however the field that comes back from the aggregate functions for the q fields is the first field in the database matching all others, how do i make it the last? --BB
Ordering By With More Specifics
Order by subname will order Chapter 1, Chapter 10, then Chapter 2. Is there a way to order them in a more appropriate fashion such as Chapter 1, Chapter 2, then Chapter 10? ORDER BY subname;
Complex Ordering
I would like to order query results by a numeric column containing positive numbers and zeros at a descending order, except that results with a zero 0 should appear last (e.g.: 2, 10, 15, 16, 0). Can I do that in a single query, or do I need to use to successive queries or order my results in the programs that receives them before displaying them?
Ordering By Value List?
I want to use a list of values for a field as the basis for a query (so far, so good - that's "WHERE col IN (comma-delimited list)"), but I want to get the results in the same order as the list of values. Is there a way?
Ordering By Company Name
I have the following PHP/MySQL code I found on a website for sorting company names alphabetically and those in the past 30 days: $result = mysql_query("SELECT quotes_needed.company FROM quotes, quotes_needed WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= quotes.date_initiated ORDER BY SUBSTRING_INDEX(quotes_needed.company, ' ', 1) ASC", $sql); while($row = mysql_fetch_assoc($result)) echo $row['company']."<br />"; It partially works. The sorting works fine but the issue is that it is dumping out 14 duplicates of each quote entry. For example, 'Acme Corporated' would be listed 15 times, then 'Bowman Industries' would be listed 15 times, then 'Chef Chu' would be listed 15 times, and so on. It should only list each quote entry once. The only thing I can figure is that it has something to do with the MySQL query.
Ordering Queries
I am running a MySQL query using product ids like this: SELECT * FROM products WHERE product_id=510 OR product_id=15 OR product_id=76 Sometimes there can be a string of up to 100 product IDs in 1 query. The problem is, I want the results to be displayed in the order that I am listing the product_ids. In other words, I want the results to be displayed in the order 510, 15, 76. However, when I run the query, the results are displayed in a different order such as 15,76,510. How can I get the results to be displayed in the order that I have passed the IDs in my query?
Ordering Results In Php
The code for displaying such holidays is: <a href="availableproduct.php?region=fiji"> Region is one of the fields in our database. Now here comes my problem. We have been asked to order the holidays for one region - i.e. Fiji, so that when they are displayed anything to do with Mana Island comes up first. Mana Island is not a region on its own and therefore is not a field. It will only be text in the title and/or description fields. Is this at all possible or no because it is not a region.
Ordering/grouping?
Say I have two fields of consequence in the same row for this operation, GUEST (varchar) and DATE (timestamp). I want to make a select where all of the identical GUEST entries are grouped together, and have their DATE be the secondary ordering criteria. Additionally, I want each set of GUEST entries to have considered their highest number DATE entry to be the criteria by which to judge how high in the returned array it should appear. little example - Note that i want the name with the highest timestamp to be first, followed by that same name with lesser timestamps (in descending order.) sam 20041005000030 sam 20041005000025 sam 20041005000020 sam 20041005000010 dan 20041005000020 dan 20041005000015 dan 20041005000010 dan 20041005000005 ian 20041005000010 ian 20041005000005 ian 20041005000002 ian 20041005000001 I realize this might take a dash of php to get done, but I know some of you guys are pretty slick at cookin up the queries, so I figure I'll let you have a go at it. :thumbsup:
Ordering A Select
When I issue a SELECT statement without ORDER BY clause, MySQL returns the rows, from the first one to the last one. Is it possible to reverse this result, that is returning the rows from the last one to the first one?
Ordering By One Collum
I want to make a MySQL query and have the results ordered by breed and THEN by regname. So basically I want it to put dogs of the same breed together and order them within their breeds alphabetically by their regname.
Ordering WHERE's Clausules
I got something like: select fields from table1 where ( (something1 like '%somethin%') OR (something2 like '%somethin%') OR (something3 like '%somethin%') OR (something4 like '%somethin%') ) I want to have the results ORDERED by first, the results of first clasule (something1) then about results of something2...and so on
Mysql Ordering
I am trying to order a plist column ASC Order But Im getting this: Plist: Plist: Plist:12 Plist:2 Plist:21 Plist:3 Plist:31 Now I was thinking this might be because my column is Varchar(255) SO I ran this: ALTER TABLE `moulds` CHANGE `plist` `plist` INT( 11 ) NOT NULL Now by some odd reason my Default Value gets set to "0" When I check My structure : plist int(11) No 0 I cannot edit that 0 out with phpmyadmin? Even if I try update. Even if I do how can I get the empty plist entries to go at the end of the result?
Ordering Of The Result
I have a table which stores information on people One of the fields that I store for each person is his nickname which is varchar(255) I notice that I have many records in the database where the nickname has a value that starts with '!'I would like to be able to sort the data in such way that entries that start with '!' will be displayed last and all the other entries will be displayed in their correct alphabetical order.Is that possible?
|
TRACKER
