[optimizing 5GB 23 million table queries]

Hi,

 

I have a table mysql table with 23million records that is over 5GB and performing select queries such as WHERE x like '%y%' is taking forever.

Using:

AppServ 2.5.10

Apache 2.2.8

PHP 5.2.6

MySQL 5.0.51b

phpMyAdmin-2.10.3

 

Software and configuration wise what can I do to run these queries faster?

There is only 1 user that needs to access it

Willing to use cloud server also

 

table is very simple:

CREATE TABLE IF NOT EXISTS `data` (

  `did` int(100) NOT NULL auto_increment,

  `dfile` varchar(255) NOT NULL,

  `0` varchar(255) NOT NULL,

  `1` varchar(255) NOT NULL,

  `2` varchar(255) NOT NULL,

  `3` varchar(255) NOT NULL,

  `4` varchar(255) NOT NULL,

  `5` varchar(255) NOT NULL,

  `6` varchar(255) NOT NULL,

  `7` varchar(255) NOT NULL,

  `8` varchar(255) NOT NULL,

  `9` varchar(255) NOT NULL,

  `10` varchar(255) NOT NULL,

  `11` varchar(255) NOT NULL,

  `12` varchar(255) NOT NULL,

  `13` varchar(255) NOT NULL,

  `14` varchar(255) NOT NULL,

  `15` varchar(255) NOT NULL,

  `16` varchar(255) NOT NULL,

  `17` varchar(255) NOT NULL,

  `18` varchar(255) NOT NULL,

  `19` varchar(255) NOT NULL,

  `20` varchar(255) NOT NULL,

  `21` varchar(255) NOT NULL,

  `22` varchar(255) NOT NULL,

  `23` varchar(255) NOT NULL,

  PRIMARY KEY  (`did`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=23415599 ;

 

Thanks in advance

 

PyraX

[Daily Sync Mysql Database]

Hey Guys,

 

Ok so here is my issue.

 

Server 1 has the mysql database and is constantly collecting data currently 148.9 MiB (win2008 server with Appserv installed)

Server 2 has all my php files and domain eg. http://www.example.com (Linux/cpanel)

 

Server 2 connects to server 1 for data on every page which causes a massive delay in page load time

Resolving domain to server 1 is not an option

 

What I want to do is great a new mysql database on Server 2 that syncs daily from Server 1 automatically

 

How do I do this? 

[simple php_match_all pattern question]

yay just got that too thanks

[simple php_match_all pattern question]

fixed it by doing

 

preg_match_all('/onclick="return false;">.*?<\/a>/',$data,$matches);

 

NEXT QUESTION

 

How do I make it so that it doesnt include onclick="return false;"> and </a> in the results?

[simple php_match_all pattern question]

its giving me the error message:

 

Warning: preg_match_all() [function.preg-match-all]: Unknown modifier 'a' in C:\AppServ\www\index.php on line 53

[simple php_match_all pattern question]

im trying to find everything between <a href="javascript:void(0);" onclick="return false;"> and </a>

 

Here is what I have tried but it didnt work

 

preg_match_all('/<a href="javascript:void(0);" onclick="return false;">.*?</a>/',$data,$matches);

[Calculating Flight Paths]

Yes thats right, cheapest cost first then shortest time

[Calculating Flight Paths]

Thanks adam, the only thing that matters is the price and the time, distance is irrelevant

[Calculating Flight Paths]

Hey everyone,

 

Im writing some code that has to work out a route based on linking airports and output all the flights it takes to get there, tried searching just dont really know what to search for

 

The data table looks like this

 

ID, Origin, Destination, DatetimeDeparting, DatetimeArriving, Price

1, BKK, CNX, 2010-10-14 12:00:00, 2010-10-14 14:00:00, 125

2, STN, BKK, 2010-10-13 18:00:00, 2010-10-13 22:00:00, 300

etc.

 

What is the easiest way of working out the path from STN to CNX via BKK?

 

UPDATE: Just to make it more interesting there will be instances where there is more than 1 path eg. STN -> KUL -> BKK -> CNX

[Google Analytics Login Curl Script For Adsense Reports]

Thanks, I have tried that one too lol. The issue I have is that I dont want to login to the api, I need to login to analytics normally then once logged in access a report via a link like:

 

https://www.google.com/analytics/reporting/export?fmt=2&id=5941183&pdr=20090829-20090928&cmp=average&rpt=ExitsReport

 

one that the API does not currently support

[Google Analytics Login Curl Script For Adsense Reports]

Thanks again, I tried those and they both did not work

[Google Analytics Login Curl Script For Adsense Reports]

Thanks redarrow but that appears to be how to locally host urchin.js not login and download a csv report, please correct me if I am wrong

[Google Analytics Login Curl Script For Adsense Reports]

Hello,

 

I am trying to make a php curl script that can login and download csv adsense performance reports that are not accessable through the API (to my knowledge).

 

The part I get stuck on is login into google analytics.

Have tried for hours using existing scripts of the net but cant seem to get them to work.

 

Can anyone help or have a script that can do this?

 

Thanks in advance

PyraX

[Know a good photo gallery script that I can intergrate that uses lightbox?]

Hi I am looking for an easy to use photo gallery script that i can integrate into my existing php CMS.

I have been seraching for hours and standalone seem to be abundant yet something simple that I can intergrate that has lightbox like presentation has been evading me.

 

Thanks,

PyraX

[[SOLVED] mysql if datetime = today question]

Thanks here was the code I ended up using

$sql = "SELECT * FROM points WHERE p_userid = '$_SESSION[uSERID]' AND p_datetime >= '".date('Y-m-d').' 00:00:00'."' AND p_datetime < '".date('Y-m-d').' 23:59:59'."' AND p_type = '1'";

[[SOLVED] mysql if datetime = today question]

hi im strugling with this

 

SELECT * FROM points

WHERE date is today without caring about the time

 

thanks

pyrax

[Setting locale time]

Works perfectly, Thank you so much

[Setting locale time]

It seems to be reverting to defaults on every page change here is my script

 

<?php 
if (!strstr(date_default_timezone_get(),"Etc/GMT")) {
	echo date_default_timezone_get(); // returns America/Denver 
if (isset($_GET['offset'])) {

	$offset = ss($_GET['offset']) / 60;
	if ($offset >= 0) {
		$offset = "+".$offset;
	}

	date_default_timezone_set('Etc/GMT'.$offset);
	$_SESSION[TIMEOFFSET] = $offset;
	echo date_default_timezone_get(); // returns Etc/GMT-10 


} else {
	echo "<script language='javascript'>\n";
	echo "var d = new Date();\n";
	echo "location.href=\"${_SERVER['SCRIPT_NAME']}?${_SERVER['QUERY_STRING']}&offset=\" + d.getTimezoneOffset();\n";
	echo "</script>\n";
	exit();
}
}
?>

[Setting locale time]

Thanks so much PFMaBiSmAd now just need to set the server time with the right offset. Do you know the command?

[Setting locale time]

Hi, I was wondering if anyone had a script that can get the locale time of the users computer so that the server can use that instead of the "America/Denver" that it seems to keep defaulting to?

 

Thanks

PyraX

[Every Friday in the month]

Question for you, how would you turn this into x number of days after a date. for example 14 days after 2008-04-18

[Every Friday in the month]

Thanks so much DJTim666

 

Here is the final code:

 

$month = $m; //supply month #
$year = $y; //supply year
$timestamp = strtotime("$m/01/$y");
$date = mktime(0, 0, 0, $month, 0, $year);
$i = 1;
$days = idate("t",$timestamp);

while ($i <= $days) {
    if (date("l",strtotime("$m/$i/$y")) == "Friday") {
        echo "Day $i is a Friday on month #$month<br>";
    }
$i++;
} 

[Every Friday in the month]

Hi I need script that gives me the day of every friday in the month given the variables year and month in an array.

 

for example this month would be

 

4,11,18,25

 

Can anyone help me with this?

 

Thanks

[recurring MONTHLY dates]

Does anyone have like a monthly calendar script?

[recurring MONTHLY dates]

Thanks AP81 but I need to know what the PHP file script is