MYSQL Cardinality?

[Monkuar]

Not be a noob but this is getting ridiculous and pissing me off..

 

 

I have this as my indexes

 

 

PRIMARY BTREE Yes No id 182 A

users_username_idx BTREE Yes No username (25) 182 A

users_registered_idx BTREE No No registered 182 A

 

Cardinality for my PRIMARY (ID IS STUCK AT 182) but my TABLE HAS 207 RECORDS, I need to update the 182 to 207 how?

 

Why? because whenever I run this query

 

$result = $db->query('SELECT COUNT(id) FROM '.$db->prefix.'users') or error('Unable to fetch total user count', __FILE__, __LINE__, $db->error());
$stats['total_users'] = $db->result($result);

 

It sucks and shows me 182, not the real "207" id's in my users table, this is really pissing me off.

 

Hell I even deleted it and re made one, still shows 182? Damn I am lost

[Pikachu2000]

Are you sure you haven't deleted 25 records?

[Monkuar]

Are you sure you haven't deleted 25 records?

 

Most likely... somewhere down the road

 

Is there any hope to restore the index primary a couple values?  :'( :'( :'( :'(

[dawsba]

you could try this

 

$result1 = $db->query('ALTER TABLE `'.$db->prefix.'users` DROP `id`;') or error('Unable to fetch total user count', __FILE__, __LINE__, $db->error());
$result2 = $db->query('ALTER TABLE `'.$db->prefix.'users` AUTO_INCREMENT = 1;') or error('Unable to fetch total user count', __FILE__, __LINE__, $db->error());
$result3 = $db->query('ALTER TABLE `'.$db->prefix.'users` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;') or error('Unable to fetch total user count', __FILE__, __LINE__, $db->error());
$result = $db->query('SELECT COUNT(id) FROM '.$db->prefix.'users') or error('Unable to fetch total user count', __FILE__, __LINE__, $db->error());
$stats['total_users'] = $db->result($result);

[Pikachu2000]

I think you misunderstand what "cardinality" means. http://en.wikipedia.org/wiki/Cardinality

[Monkuar]

you could try this

 

$result1 = $db->query('ALTER TABLE `'.$db->prefix.'users` DROP `id`;') or error('Unable to fetch total user count', __FILE__, __LINE__, $db->error());
$result2 = $db->query('ALTER TABLE `'.$db->prefix.'users` AUTO_INCREMENT = 1;') or error('Unable to fetch total user count', __FILE__, __LINE__, $db->error());
$result3 = $db->query('ALTER TABLE `'.$db->prefix.'users` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;') or error('Unable to fetch total user count', __FILE__, __LINE__, $db->error());
$result = $db->query('SELECT COUNT(id) FROM '.$db->prefix.'users') or error('Unable to fetch total user count', __FILE__, __LINE__, $db->error());
$stats['total_users'] = $db->result($result);

 

thanks for the code but something tells me that would hurt performance once my member's is in the thousands lol   im just looking to fix it through mysql first then keep using

$result = $db->query('SELECT COUNT(id) FROM '.$db->prefix.'users') or error('Unable to fetch total user count', __FILE__, __LINE__, $db->error());

whenever

 

thanks tho aha

 

 

i just know it doesn't match with my 207 id's in my table so theres something wrong, and i am looking on a way to fix it through mysql   i don't want to run 3 extra queries man

[Pikachu2000]

There is absolutely no reason to try to manipulate the values of the primary key of a database table to suit your fancy. Doing so has the potential to utterly destroy the referential integrity of the database.

[Monkuar]

There is absolutely no reason to try to manipulate the values of the primary key of a database table to suit your fancy. Doing so has the potential to utterly destroy the referential integrity of the database.

 

 

 

i have 207 ROWS...................

 

i count them, I have legimately 207 ROWS of id's but the cardinality is showing 187.............. there is something wrong here, how do I change the 187 to 207 to match how many id's I have in my table that's all I am asking 

[Monkuar]

Since I did delete 25 id's

 

i'll just use

$result = $db->query('SELECT COUNT(id) FROM '.$db->prefix.'users') or error('Unable to fetch total user count', __FILE__, __LINE__, $db->error());
$stats['total_users'] = $db->result($result);
$newstats= $stats['total_users']+25;

 

topic solved, lol thanks all

 

[Pikachu2000]

Unless I'm really missing something here, I have my doubts that you actually have 207 records, since both the SELECT COUNT() and cardinality values are showing 182. Insert a new record and see what value its primary key has.

[Monkuar]

Unless I'm really missing something here, I have my doubts that you actually have 207 records, since both the SELECT COUNT() and cardinality values are showing 182. Insert a new record and see what value its primary key has.

it was my fault, i apologize.. i was looking through the records in descending order and found like

 

10

7

6

5

4

1

 

so it does seem like I did delete some, (no idea why it was my fault)