[PHP-DB] Re: Problem with mysql and php
Jason Pruim wrote:
> Given the following 2 queries:
>
> "SELECT DISTINCT areacode FROM main WHERE state = '{$query_exploded[0]}'";
>
> "SELECT DISTINCT areacode FROM main";
>
> The second displays ALOT faster Like by minutes... the first one is
> what I really want though Currently working with a dataset of 89
> million records, will be expanding that to many many more times that... To
> the tune of possibly a couple billion records...
>
> Any ideas? :)
>
>
> Jason Pruim
> li...@pruimphotography.com
Absolutely the first thing I would do is an EXPLAIN on the slow query, and
work from the results of that. I'd hazard a guess that there might be
indexing problems.
http://dev.mysql.com/doc/refman/5.1/en/using-explain.html
Cheers
--
David Robley
Insert inevitable trivial witticism of your choice.
Today is Pungenday, the 41st day of The Aftermath in the YOLD 3177.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sqlite
David McGlone wrote:
> places.sqlite is mozilla's bookmarks db and I was trying to read this
> db, but so far I've been unsuccessful.
>
> sqlite ver: 2.8.17
> PHP version: PHP 5.3.3-1ubuntu9.6 with Suhosin-Patch
> php5-sqlite: 5.3.3-1ubuntu9.6
Okay, first off, mozilla uses sqlite3, not sqlite2, so you need to use
those functions/classes instead.
> try
> {
> //create or open the database
> $database = new SQLiteDatabase('places.sqlite', 0666, $error);
> }
> catch(Exception $e)
> {
> die($error);
> }
>
> $query = "SELECT * FROM moz_bookmarks";
>
> if($result = $database->query($query, SQLITE_BOTH, $error))
> {
> while($row = $result->fetch())
> {
> echo ("ID: {$row['id']} " );
> }
> }
> else
> {
> die($error);
> }
>
Using SQLite3, this works, dumping the first record:
query("select * from moz_bookmarks");
var_dump($result->fetchArray());
> But if I use this code:
>
> try
> {
> /*** connect to SQLite database ***/
>
> $dbh = new PDO("sqlite:places.sqlite");
> echo "Handle has been created .. ";
>
>
> }
> catch(PDOException $e)
> {
> echo $e->getMessage();
> echo "Database -- NOT -- loaded successfully .. ";
> die( "Query Closed !!! $error");
> }
>
> echo "Database loaded successfully ";
>
> I get the expected output, but can't figure out how to change the above
> script to echo the contents of the DB. I just get the two messages and
> that's it.
This works as it does because PDO uses SQLite3.
Read http://us.php.net/manual/en/book.pdo.php thoroughly.
To submit a query using PDO, it's just:
$result = $dbh->query('select * from moz_bookmarks');
which returns an object of PDOStatement class in $result. Then you can
just loop on $result->fetch() to deal with each row.
This is a PDO version of the above:
try {
$db = new PDO('sqlite:places.sqlite');
}
catch (PDOException $e) {
die("SQLite connection failed ".$e->getMessage());
}
if ($result = $db->query("select * from moz_bookmarks")) {
var_dump($result->fetch());
} else {
die("Query failed.");
}
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Problem with mysql and php
Jason Pruim wrote:
> Given the following 2 queries:
>
> "SELECT DISTINCT areacode FROM main WHERE state = '{$query_exploded[0]}'";
>
> "SELECT DISTINCT areacode FROM main";
>
> The second displays ALOT faster Like by minutes... the first one is what
> I really want though Currently working with a dataset of 89 million
> records, will be expanding that to many many more times that... To the tune
> of possibly a couple billion records...
>
> Any ideas? :)
make state an index into the table.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Problem with mysql and php
Try running this : http://mysqltuner.pl/mysqltuner.pl
If the server has been up for over 24hrs it gives a lot of good insight and
things to try tuning wise as a starting point. It'll uncover a lot of
common configuration issues or MySQL server level bottlenecks.
-Dan
(apologizes for the top post from the mobile)
On Nov 29, 2011 5:33 PM, "Tamara Temple"
wrote:
> Jason Pruim wrote:
>
> > Given the following 2 queries:
> >
> > "SELECT DISTINCT areacode FROM main WHERE state =
> '{$query_exploded[0]}'";
> >
> > "SELECT DISTINCT areacode FROM main";
> >
> > The second displays ALOT faster Like by minutes... the first one is
> what I really want though Currently working with a dataset of 89
> million records, will be expanding that to many many more times that... To
> the tune of possibly a couple billion records...
> >
> > Any ideas? :)
>
> make state an index into the table.
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
Re: [PHP-DB] Problem with mysql and php
Didn't the OP begin this very same subject a month ago? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Problem with mysql and php
> From: joker_mos...@hotmail.com > To: phps...@gmail.com > Date: Tue, 29 Nov 2011 23:07:13 -0500 > CC: phildob...@gmail.com; php-db@lists.php.net > Subject: Re: [PHP-DB] Problem with mysql and php > > > > Le 2011-11-28 à 22:38, Bastien Koert a écrit : > > > On Mon, Nov 28, 2011 at 9:19 PM, Phil Dobbin > > wrote: > >> On 29/11/11 02:08, "Jason Pruim" wrote: > >> > PostgreSQL? > > ;-)... > >>> > >>> In all seriousness... Would it help or change it in anyway? :) > >>> > >>> I am free to use what I want (I believe) on this project... > >> > >> It's well worth looking into. Postgres can handle far bigger db's > >> much > >> quicker than MySQL but the downside is that it's a very steep > >> learning curve > >> after coming from mysql. > >> > >> It's relatively easy to install & there are the drivers of course > >> for PHP > >> but it'll take up a lot of your time to learn it to the extent of > >> being > >> confident with it in my experience. > >> > >> Good luck, > >> > >> Cheers, > >> > >>Phil... > >> -- > >> Nothing to see here... move along, move along > >> > >> > >> -- > >> PHP Database Mailing List (http://www.php.net/) > >> To unsubscribe, visit: http://www.php.net/unsub.php > >> > >> > > > > jason, > > > > Assuming you have indexes on the data properly, have you looked into > > the mysql settings to ensure that you have the ones for large or xl > > datasets? There are a number of settings for buffers and sort spaces > > that can tune the database for performance > > > > http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow-with-large-tables/ > > > > Also, what kind of hard ware are you using? Does the db server have > > oodles (yeah that techie term) of RAM? > > > > Have you looked into creating views for each state? If the db has a > > fairly static dataset (only adding not much updating) then you create > > those views so that you are then doing a single select against a > > pre-processed dataset. > > > > More tuning can be done by sharding the data across different > > diskdrives to aid i/o. > > > > A great book on mysql performance is High Peformance MySQL > > http://www.amazon.com/dp/0596101716?tag=xaprb-20 which is chockful of > > great options and info about gaining performance. > > > > > > > > -- > > > > Bastien > > > > Cat, the other other white meat > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > You can try nosql :) > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > You can try nosql :)
Re: [PHP-DB] sqlite
On Tue, 2011-11-29 at 16:31 -0600, Tamara Temple wrote: > David McGlone wrote: > > places.sqlite is mozilla's bookmarks db and I was trying to read this > > db, but so far I've been unsuccessful. > > > > sqlite ver: 2.8.17 > > PHP version: PHP 5.3.3-1ubuntu9.6 with Suhosin-Patch > > php5-sqlite: 5.3.3-1ubuntu9.6 > > Okay, first off, mozilla uses sqlite3, not sqlite2, so you need to use > those functions/classes instead. Thank you Tamara.. I'll see what I can accomplish. I'm thinking even if I do it this way, I'll still have to restart firefox for the page to update. -- Thanks, David M. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Problem with mysql and php
Jim, Similar yes... But this was specifically about replacing distinct with something since it was taking WAY to long... But it did evolve into a very similar conversation :) Jason Pruim pru...@gmail.com On Nov 29, 2011, at 6:25 PM, Jim Giner wrote: > Didn't the OP begin this very same subject a month ago? > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
