Given a table of N records, what would be considered the fastest way of getting a single random PK from that table(for later use in php)?  The two methods I'm wondering about are : mysql : perform the random query directly and get a single ID as the query result. php   : get a list of IDs from mysql and use array_rand() to fetch one at random  Also : Does scale matter for such a query? Would a table N=200 records be faster using mysql but a table of N=200,000 be faster with php?(or vice verca)    My intuition tells me that MySQL should be faster and use much less memory, as you must create an array of N keys using the php method and store them at least temporarily.
60461
What is a fast way to select a random row from a large mysql table?  I'm working in php, but I'm interested in any solution even if it's in another language.
105008
Is there a way to get the count of rows in all tables in a MySQL database without running a SELECT count() on each table?
97492
I have a MySQL database table with nearly 23 million records. This table has no primary key, because nothing is unique. It has 2 columns, both are indexed. Below is its structure:    Below is some of its data:    Now, I ran a simple query:  SELECT `indexVal` FROM `key_word` WHERE `hashed_word`='001'   Unfortunately, this took more than 5 seconds to retrieve the data and show them to me. My future table will have 150 billion records, so this time is very very high.  I ran the Explain command to see what's going on. Result is below.    Then I ran the Profile using below command.  SET profiling=1; SELECT `indexVal` FROM `key_word` WHERE `hashed_word` = '001'; SHOW profile;   Below is the result of the profiling:    Below is some more information about my table:    So, why this is taking so long? They are indexed too! In future, I have to run lot of LIKE commands, so this is taking too much time. What has gone wrong?
58273
I know that php has md5(), sha1(), and the hash() functions, but I want to create a hash using the MySQL PASSWORD() function.  So far, the only way I can think of is to just query the server, but I want a function (preferably in php or Perl) that will do the same thing without querying MySQL at all.  For example:  MySQL hash -> 464bb2cb3cf18b66  MySQL5 hash -> *01D01F5CA7CA8BA771E03F4AC55EC73C11EFA229  Thanks!
86789
I don't have this kind of ID in my table, still MySQL is giving me a result.  This same result is giving when i place in my php code.
104158
I wanted to get some UI design advice. I am not sure this is the proper forum to ask this question so here goes.  My situation is I have a table that has 51 bit values to hold the values of statutes.     In production, this table would have over 10,000 records for the different LocationID's.  The UI for this in its pure database for would have a page with the dozens of text boxes.  Is there a better, cleaner way to do this?  Any recommendation or links to how others have handled this situation.  Please feel free to post any additional questions if further clarification is needed.
41532
I have information spread out across a few databases and want to put all the information onto one webpage using PHP.  I was wondering how I can connect to multiple databases on a single PHP webpage.  I know how to connect to a single database using:  $dbh = mysql_connect($hostname, $username, $password)          or die("Unable to connect to MySQL");   However, can I just use multiple "mysql_connect" commands to open the other databases, and how would PHP know what database I want the information pulled from if I do have multiple databases connected.
36042
Duplicate:    I want an pseudo random number generator that can generate numbers with no repeats in a random order. For example: random(10) might return 5, 9, 1, 4, 2, 8, 3, 7, 6, 10 Is there a better way to do it other than making the range of numbers and shuffling them about, or checking the generated list for repeats?  Edit: Also I want it to be efficient in generating big numbers without the entire range.  Edit: I see everyone suggesting shuffle algorithms. But if I want to generate large random number (1024 byte+) then that method would take alot more memory than if I just used a regular RNG and inserted into a Set until it was a specified length, right? Is there no better mathematical algorithm for this.
49941
I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases:    SQL Server MySQL PostgreSQL SQLite Oracle   I am currently doing something like the following in SQL Server 2005, but I'd be interested in seeing other's more agnostic approaches:  WITH Ordered AS ( SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate FROM Orders) SELECT * FROM Ordered WHERE RowNumber = 1000000   Credit for the above SQL:   Update: See  regarding the SQL standard. Troels, have you got any links we can cite?
78721
A few months back I was tasked with implementing a unique and random code for our web application.  The code would have to be user friendly and as small as possible, but still be essentially random (so users couldn't easily predict the next code in the sequence).  It ended up generating values that looked something like this:  Af3nT5Xf2   Unfortunately, I was never satisfied with the implementation.  Guid's were out of the question, they were simply too big and difficult for users to type in.  I was hoping for something more along the lines of 4 or 5 characters/digits, but our particular implementation would generate noticeably patterned sequences if we encoded to less than 9 characters.  Here's what we ended up doing:  We pulled a unique sequential 32bit id from the database.  We then inserted it into the center bits of a 64bit RANDOM integer.  We created a lookup table of easily typed and recognized characters (A-Z, a-z, 2-9 skipping easily confused characters such as L,l,1,O,0, etc.).  Finally, we used that lookup table to base-54 encode the 64-bit integer.  The high bits were random, the low bits were random, but the center bits were sequential.  The final result was a code that was much smaller than a guid and looked random, even though it absolutely wasn't.  I was never satisfied with this particular implementation.  What would you guys have done?
30257
I have a table:  table votes (     id,     user,     email,     address,     primary key(id), );   Now I want to make the columns user, email, address unique (together).  How do I do this in MySql?  Of course the example is just... an example. So please don't worry about the semantics.
105454
I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level but all are abstracted by the stored proc call.  I'm trying to slam the DB to see what the breaking point (# of requests per second) would be.   In a nutshell, I have a table such as this userId, newMsgCount with a clustered index on userId. SQL should be able to server hundreds or thousands of these responses per second. I think the laggard is my .NET app.  How can I make this a good test to achieve the test results based on SQL performance?  Is there a tool for this that i can give it a stored proc name and param for it to pund my  DB?  I want to see if the DB can return a min. of 250 responses per second.
46568
I came across PHP way of doing the trick:  my_bool reconnect = 1; mysql_options(&amp;mysql, MYSQL_OPT_RECONNECT, &amp;reconnect);   but no luck with MySQLdb (python-mysql).  Can anybody please give a clue? Thanks.
6195
Using PHP, what's the fastest way to convert a string like this: "123" to an integer?  Why is that particular method the fastest? What happens if it gets unexpected input, such as "hello" or an array?
48161
I am not very familiar with databases and the theories behind how they work.  Is it any slower from a performance standpoint (inserting/updating/querying) to use Strings for Primary Keys than integers?
85863
I've got a table of URLs and I don't want any duplicate URLs. How do I check to see if a given URL is already in the table using PHP/MySQL?
94051
I want to do something like this:    create table app_users (     app_user_id smallint(6) not null auto_increment primary key,     api_key     char(36) not null default uuid() );    However this results in a error, is there a way to call a function for a default value in mysql?  thanks.