Hooker

1, 'SIMPLE', 'test_suppress', 'index', 'Surname,Postcode,test', 'db', '10', '', 29932, 'Using index' 1, 'SIMPLE', 'tbr', 'ref', 'Surname,Postcode,test', 'Postcode', '10', 'db.test_suppress.Postcode', 1, 'Using where'

Yes, when actualy generating the view it runs in 0.2 seconds and uses the right indexes but as soon as i try to query it after it totaly crawls.

I was just wondering if it were possible to select the content's of a view into a table efficiently? I'm currently generating the view like so: CREATE OR REPLACE VIEW initial_check AS SELECT LEFT(tbr.Forename, 1) AS Forename, tbr.Surname, LEFT(tbr.Addr1, 5) AS Addr1, tbr.Postcode FROM test_suppress INNER JOIN tbr ON (LEFT(test_suppress.forename, 1)=LEFT(tbr.forename, 1) AND test_suppress.surname=tbr.surname AND LEFT(test_suppress.addr1, 5)=LEFT(tbr.addr1, 5) AND test_suppress.postcode=tbr.postcode) Which only adds around 375 rows to the view but when i go to select the contents it takes 27 seconds to pull it all out.

Thanks, this is the level of thought i have at 5am with the flu.

Hello, I'm sure this is a fairly easy problem to solve but i can't seem to find alot of information on it, is there anyway to read the column headers (and just the headers) in a CSV file? i'm writing an automised system that requires a person to identify which headers are which but the csv files are millions of rows and reading the entire file isn't practical. If anyone could help me i'd really appreciate it. Thanks

Take a look into "LAST_INSERT_ID()"

It's not absolutely necessary because if i remember correctly php handles that itself but its always best practice to close a connection when you're done.

You're missing a vital peice of fenway's example. When you do a left join it doesn't just select matching rows, it selects everything from the left table, if the field you choose to join the tables on don't match it will simply add a NULL in the field of the right table (the table being joined) so: SELECT a . * FROM RQquestions AS q LEFT JOIN RQanswered AS a ON ( q.ID = a.qID ) WHERE a.userID = '1180540351' AND WHERE a.qID IS NULL ORDER BY RAND( ) Should do it.

more of a PHP question tha mysql..

Can we see your table structures? I think i understand what you want but without seeing how its all set out its difficult to say what your best bet is.

SELECT * FROM table WHERE animaltype = 'owl' AND animalcolor = 'blue' ORDER BY id DESC; No multiple ORDER BY's needed, deffinately start reading MYSQL books because thats about as basic as it gets.

Can i ask why you have the WHERE clause? is emailID auto inc or are you just using it as a boolen? because if its auto inc you're checking what exactly? Honestly i've seen mysql get bitchy about less in my time so its worth a try removing it if it is auto inc.

If you've got a few spare years you could try to bruteforce it

It can vary quite a bit but i'd reserve around 1.5 - 2gig for it, atleast 'till you have a few records in to get a good estimate.

Around about 612gig with packed keys. It does hold quite detailed information though.

It depends on your query speed, server settings and on the spec's of the server but aslong as you get everything right you should scale out very well. I've just finnished a system that looks up everyone in the UK at every known address for the last ten years consisting over over 680 million records and the avrage look up takes about 5.6 secs (im still tweaking).

Can you show your MYSQL table? seem's a little strange to have to define the column with a variable. If you're trying to find out if the website exists in your database and if it's active this is what i'd probably do to make your code work: * First, instead of just the site name, have the site name (in this instance the column is called "website" in the table) and another field that would be a boolen called "active". <?php include"mysql.php"; $site = $_POST['site']; $site = $_POST['active']; // Get all the data from the "example" table $sql = "SELECT * FROM listings WHERE website=$site AND active=$active"; $result = mysql_query($sql)or die(mysql_error()); // keeps getting the next row until there are no more to get if (mysql_num_rows($result)) { while($row = mysql_fetch_array( $result )) { // Print out the contents of each row echo $row['title']; } } ?> This would then select only the active websites that it finds in the database. Note: to choose if you want active or inactive websites you simply change the value of "$active" to 1 or 0 respectively.

Just a couple of questions: * Is that your full source? * Are you getting any errors? It looks like you're pulling the data, just not using it.

It really depends on how many fields you have in your table, can you show us the structure? Idealy if your going to go into the hundreds of thousands - millions of records you should idealy be looking to split your table where nessisary.

It's a litle long winded but you can do this for each month: UPDATE yourTable SET theMonthField='11' WHERE theMonthField='November';[/Code]

Ive gotto agre with fenway here, i see it all the time in work when a client sends in data i have to spend hours sorting it out due to bad logic on the part of the client. Give storing your dates properly some serious thought (sooner rather than later!), it'l save you alot of hastle in the long run.

Can i ask one thing, how different are the structures for the "Male" and "Female" tables.

yep, was just trying to make it a little more understandable than "try replication" as its probably a better option than a straight backup of a live table.

You could always create a duplicate set of tables (ie. table1 = table1_backup) and select table1 into table1_backup then run your backup tool (and obviously empty "table1_backup" on completion. You might take a little hit on performance for the duration of the select but it would allow you to do live backups with little risk.

Yes, a game.. still in planning, peicing things together before writing it all out but i'm trying to make it as graphical as possible (including maps showing where players are etc). This part would basicaly be for how far a person can see as they move around and start villages etc Nothing huge, just something to keep myself busy